

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=&#34;auto&#34;>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.png">
  <link rel="icon" type="image/png" href="/img/favicon.png">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="CodeHope">
  <meta name="author" content="CodeHope">
  <meta name="keywords" content="希望">
  <title>MYSQL - CodeHope</title>

  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/github-markdown-css@4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    
    
      
      
        
          
          
          
        
        <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/prismjs@1.22.0/themes/prism-tomorrow.min.css" />
      
      
        <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/prismjs@1.22.0/plugins/line-numbers/prism-line-numbers.min.css" />
      
    
  

  
    <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.css" />
  



<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_ba1fz6golrf.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_kmeydafke9r.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    var CONFIG = {"hostname":"quancundexiwang.wang","root":"/","version":"1.8.7","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"left","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":true,"trickleSpeed":100}},"copy_btn":true,"image_zoom":{"enable":true},"toc":{"enable":true,"headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"onlypost":true},"web_analytics":{"enable":false,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":"Mi65hxq7VAFUDwOLeIGAOgiV-gzGzoHsz","app_key":"hMuhiD4FRqhns4giqLiEH9HG","server_url":null}}};
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
<meta name="generator" content="Hexo 5.3.0"></head>


<body>
  <header style="height: 80vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/">&nbsp;<strong>CodeHope</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                村头
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                我
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;</a>
          </li>
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" href="javascript:">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="banner" id="banner" parallax=true
         style="background: url('https://ss3.bdstatic.com/70cFv8Sh_Q1YnxGkpoWK1HF6hhy/it/u=1313430092,4065359957&fm=26&gp=0.jpg') repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="page-header text-center fade-in-up">
            <span class="h2" id="subtitle" title="MYSQL">
              
            </span>

            
              <div class="mt-3">
  
  
    <span class="post-meta">
      <i class="iconfont icon-date-fill" aria-hidden="true"></i>
      <time datetime="2020-08-26 22:13" pubdate>
        2020年8月26日 晚上
      </time>
    </span>
  
</div>

<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      14.8k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      195
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
            <div class="scroll-down-bar">
              <i class="iconfont icon-arrowdown"></i>
            </div>
          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div class="py-5" id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">MYSQL</h1>
            
            <div class="markdown-body">
              <h1 id="MYSQL-学习"><a href="#MYSQL-学习" class="headerlink" title="MYSQL 学习"></a>MYSQL 学习</h1><h2 id="1-常用数据类型"><a href="#1-常用数据类型" class="headerlink" title="1.常用数据类型"></a>1.常用数据类型</h2><h3 id="1-1数值类型"><a href="#1-1数值类型" class="headerlink" title="1.1数值类型"></a>1.1数值类型</h3><blockquote>
<p>MySQL支持所有标准SQL数值数据类型。</p>
</blockquote>
<blockquote>
<p>这些类型包括严格数值数据类型(<code>INTEGER</code>、<code>SMALLINT</code>、<code>DECIMAL</code>和<code>NUMERIC</code>)，以及近似数值数据类型(<code>FLOAT</code>、<code>REAL</code>和<code>DOUBLE PRECISION</code>)。</p>
</blockquote>
<blockquote>
<p>关键字<code>INT</code>是<code>INTEGER</code>的同义词，关键字<code>DEC</code>是<code>DECIMAL</code>的同义词。</p>
</blockquote>
<blockquote>
<p>BIT数据类型保存位字段值，并且支持<code>MyISAM、MEMORY、InnoDB</code>和<code>BDB</code>表。</p>
<p>作为SQL标准的扩展，MySQL也支持整数类型<code>TINYINT</code>、<code>MEDIUMINT</code>和<code>BIGINT</code>。下面的表显示了需要的每个整数类型的存储和范围。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">类型</th>
<th align="left">大小</th>
<th align="left">范围（有符号）</th>
<th align="left">范围（无符号）</th>
<th align="left">用途</th>
</tr>
</thead>
<tbody><tr>
<td align="left">TINYINT</td>
<td align="left">1 byte</td>
<td align="left">(-128，127)</td>
<td align="left">(0，255)</td>
<td align="left">小整数值</td>
</tr>
<tr>
<td align="left">SMALLINT</td>
<td align="left">2 bytes</td>
<td align="left">(-32 768，32 767)</td>
<td align="left">(0，65 535)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">MEDIUMINT</td>
<td align="left">3 bytes</td>
<td align="left">(-8 388 608，8 388 607)</td>
<td align="left">(0，16 777 215)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">INT或INTEGER</td>
<td align="left">4 bytes</td>
<td align="left">(-2 147 483 648，2 147 483 647)</td>
<td align="left">(0，4 294 967 295)</td>
<td align="left">大整数值</td>
</tr>
<tr>
<td align="left">BIGINT</td>
<td align="left">8 bytes</td>
<td align="left">(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td>
<td align="left">(0，18 446 744 073 709 551 615)</td>
<td align="left">极大整数值</td>
</tr>
<tr>
<td align="left">FLOAT</td>
<td align="left">4 bytes</td>
<td align="left">(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td>
<td align="left">0，(1.175 494 351 E-38，3.402 823 466 E+38)</td>
<td align="left">单精度 浮点数值</td>
</tr>
<tr>
<td align="left">DOUBLE</td>
<td align="left">8 bytes</td>
<td align="left">(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td align="left">0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td align="left">双精度 浮点数值</td>
</tr>
<tr>
<td align="left">DECIMAL</td>
<td align="left">对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td>
<td align="left">依赖于M和D的值,DECIMAL(10,2) ：<code>2</code>表示精确到小数后2位，<code>10</code>表示整个数字的字符长度为10</td>
<td align="left">依赖于M和D的值</td>
<td align="left">小数值</td>
</tr>
</tbody></table>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201202102931.png" srcset="/img/loading.gif"></p>
<h3 id="1-2日期和时间类型"><a href="#1-2日期和时间类型" class="headerlink" title="1.2日期和时间类型"></a>1.2日期和时间类型</h3><blockquote>
<p>表示时间值的日期和时间类型为<code>DATETIME</code>、<code>DATE</code>、<code>TIMESTAMP</code>、<code>TIME和YEAR</code>。</p>
<p>每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。</p>
<p>TIMESTAMP类型有专有的自动更新特性，将在后面描述。</p>
</blockquote>
<table>
<thead>
<tr>
<th align="left">类型</th>
<th align="left">大小</th>
<th align="left">范围</th>
<th align="left">格式</th>
<th align="left">用途</th>
</tr>
</thead>
<tbody><tr>
<td align="left">DATE</td>
<td align="left">3bytes</td>
<td align="left">1000-01-01/9999-12-31</td>
<td align="left">YYYY-MM-DD</td>
<td align="left">日期值</td>
</tr>
<tr>
<td align="left">TIME</td>
<td align="left">3bytes</td>
<td align="left">‘-838:59:59’/‘838:59:59’</td>
<td align="left">HH:MM:SS</td>
<td align="left">时间值或持续时间</td>
</tr>
<tr>
<td align="left">YEAR</td>
<td align="left">1bytes</td>
<td align="left">1901/2155</td>
<td align="left">YYYY</td>
<td align="left">年份值</td>
</tr>
<tr>
<td align="left">DATETIME</td>
<td align="left">8bytes</td>
<td align="left">1000-01-01 00:00:00/9999-12-31 23:59:59</td>
<td align="left">YYYY-MM-DD HH:MM:SS</td>
<td align="left">混合日期和时间值</td>
</tr>
<tr>
<td align="left">TIMESTAMP</td>
<td align="left">4bytes</td>
<td align="left">1970-01-01 00:00:00/2038结束时间是第 <strong>2147483647</strong> 秒，北京时间 <strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</td>
<td align="left">YYYYMMDD HHMMSS</td>
<td align="left">混合日期和时间值，时间戳</td>
</tr>
</tbody></table>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201202105416.png" srcset="/img/loading.gif" alt="image-20201202105416706"></p>
<h3 id="1-3字符串类型"><a href="#1-3字符串类型" class="headerlink" title="1.3字符串类型"></a>1.3字符串类型</h3><blockquote>
<p>字符串类型指<code>CHAR</code>、<code>VARCHAR</code>、<code>BINARY</code>、<code>VARBINARY</code>、<code>BLOB</code>、<code>TEXT</code>、<code>ENUM</code>和<code>SET</code>。该节描述了这些类型如何工作以及如何在查询中使用这些类型。</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201202105824.png" srcset="/img/loading.gif" alt="image-20201202105824048"></p>
<p><strong>注意：</strong></p>
<ul>
<li><strong><code>char(n) 和 varchar(n) </code>中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。</strong></li>
<li><strong><code>CHAR</code> 和 <code>VARCHAR</code> 类型类似，但它们保存和检索的方式不同。它们的<code>最大长度</code>和<code>是否尾部空格被保留</code>等方面也不同。在存储或检索过程中不进行大小写转换。</strong></li>
<li><strong><code>BINARY</code> 和 <code>VARBINARY </code>类似于 <code>CHAR</code> 和<code> VARCHAR</code>,不同的是它们包含二进制字符串而不要非二进制字符串,也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。</strong></li>
<li><strong><code>BLOB</code> 是一个<code>二进制大对象</code>，可以容纳可变数量的数据。有 4 种 BLOB 类型：<code>TINYBLOB</code>、<code>BLOB</code>、<code>MEDIUMBLOB </code>和 <code>LONGBLOB</code>。它们区别在于可容纳存储范围不同。</strong></li>
<li><strong>4 种 <code>TEXT</code> 类型：<code>TINYTEXT</code>、<code>TEXT</code>、<code>MEDIUMTEXT</code> 和 <code>LONGTEXT</code>。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。</strong></li>
</ul>
<h2 id="2-基础的DDL-SQL语法-表结构"><a href="#2-基础的DDL-SQL语法-表结构" class="headerlink" title="2.基础的DDL SQL语法(表结构)"></a>2.基础的DDL SQL语法(表结构)</h2><blockquote>
<p>数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要<a target="_blank" rel="noopener" href="https://baike.baidu.com/item/%E5%AD%98%E5%82%A8/1582924">存储</a>的现实世界实体的语言。</p>
<p>通俗点说，主要用在定义数据表结构的sql语句</p>
</blockquote>
<h3 id="2-1-MySQL-创建数据表"><a href="#2-1-MySQL-创建数据表" class="headerlink" title="2.1 MySQL 创建数据表"></a>2.1 MySQL 创建数据表</h3><p>创建MySQL数据表需要以下信息：</p>
<ul>
<li>表名</li>
<li>表字段名</li>
<li>定义每个表字段</li>
</ul>
<p>语法,以下为创建MySQL数据表的SQL通用语法：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> table_name <span class="token punctuation">(</span>column_name column_type<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>essay<span class="token punctuation">`</span><span class="token punctuation">(</span>
   <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
   <span class="token punctuation">`</span>title<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
   <span class="token punctuation">`</span>author<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
   <span class="token punctuation">`</span><span class="token keyword">date</span><span class="token punctuation">`</span> <span class="token keyword">DATE</span><span class="token punctuation">,</span>
   <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span> <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<ul>
<li>如果你不想字段为 <strong>NULL</strong> 可以设置字段的属性为 <strong>NOT NULL</strong>， 在操作数据库时如果输入该字段的数据为<strong>NULL</strong> ，就会报错。</li>
<li><code>AUTO_INCREMENT</code>定义列为自增的属性，一般用于主键，数值会自动加1。</li>
<li><code>PRIMARY KEY</code>关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。</li>
<li><code>ENGINE </code>设置存储引擎，<code>CHARSET</code> 设置编码。</li>
</ul>
<h3 id="2-2-MySQL-删除数据表"><a href="#2-2-MySQL-删除数据表" class="headerlink" title="2.2.MySQL 删除数据表"></a>2.2.MySQL 删除数据表</h3><blockquote>
<p>MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失</p>
</blockquote>
<p>语法,以下为删除MySQL数据表的通用语法：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> table_name <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h3 id="2-3-MySQL-插入新的数据表字段"><a href="#2-3-MySQL-插入新的数据表字段" class="headerlink" title="2.3.MySQL 插入新的数据表字段"></a>2.3.MySQL 插入新的数据表字段</h3><p>语法:</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> tableName

<span class="token keyword">ADD</span> <span class="token keyword">column</span> <span class="token keyword">type</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> student
<span class="token punctuation">(</span>
    id   <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    name <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span>                      <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">DESC</span> student<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>我们创建一个student的表</p>
<table>
<thead>
<tr>
<th>Field</th>
<th>Type</th>
<th>Null</th>
<th>Key</th>
<th>Default</th>
<th>Extra</th>
</tr>
</thead>
<tbody><tr>
<td>id</td>
<td>int(11)</td>
<td>NO</td>
<td>PRI</td>
<td>NULL</td>
<td>auto_increment</td>
</tr>
<tr>
<td>name</td>
<td>char(255)</td>
<td>NO</td>
<td></td>
<td>NULL</td>
<td></td>
</tr>
</tbody></table>
<p>我们想往里面插入新的表字段 gender</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> student

<span class="token keyword">ADD</span> gender <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>

<span class="token keyword">DESC</span> student<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>int(11)</th>
<th>NO</th>
<th>PRI</th>
<th>NULL</th>
<th>auto_increment</th>
</tr>
</thead>
<tbody><tr>
<td>name</td>
<td>char(255)</td>
<td>NO</td>
<td></td>
<td>NULL</td>
<td></td>
</tr>
<tr>
<td>Field</td>
<td>Type</td>
<td>Null</td>
<td>Key</td>
<td>Default</td>
<td>Extra</td>
</tr>
<tr>
<td>gender</td>
<td>varchar(100)</td>
<td>NO</td>
<td></td>
<td>NULL</td>
<td></td>
</tr>
</tbody></table>
<h3 id="2-4-MYSQL-修改表头字段"><a href="#2-4-MYSQL-修改表头字段" class="headerlink" title="2.4 MYSQL 修改表头字段"></a>2.4 MYSQL 修改表头字段</h3><p><code>CHANGE </code>对列进行重命名或更改列的类型，需给定旧的列名称和新的列名称、当前的类型<br><code>MODIFY </code>可以改变列的类型，此时不需要重命名（不需给定新的列名称）</p>
<p>一句话 修改名词+类型用change，只修改类型使用modify就可以了</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> student
    CHANGE gender gender_s <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>
    <span class="token keyword">MODIFY</span> gender_s <span class="token keyword">enum</span> <span class="token punctuation">(</span><span class="token string">'male'</span><span class="token punctuation">,</span><span class="token string">'female'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<h3 id="2-5-MYSQL-删除表头字段"><a href="#2-5-MYSQL-删除表头字段" class="headerlink" title="2.5.MYSQL 删除表头字段"></a>2.5.MYSQL 删除表头字段</h3><p>语法：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> 表名
	<span class="token keyword">DROP</span> 字段名<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> student
    	<span class="token keyword">DROP</span> gender_s<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> student<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>常昊</td>
</tr>
<tr>
<td>2</td>
<td>常昊</td>
</tr>
<tr>
<td>3</td>
<td>材质红</td>
</tr>
<tr>
<td>99</td>
<td>常昊</td>
</tr>
</tbody></table>
<h2 id="3-数据库的范式"><a href="#3-数据库的范式" class="headerlink" title="3.数据库的范式"></a>3.数据库的范式</h2><blockquote>
<p>构造数据库必须遵循一定的规则，这种规则就是范式。</p>
</blockquote>
<blockquote>
<p>目前关系型数据库有六种范式，一般情况下，只满足第三范式即可。</p>
</blockquote>
<h3 id="3-1-第一范式-原子性"><a href="#3-1-第一范式-原子性" class="headerlink" title="3.1 第一范式-原子性"></a>3.1 第一范式-原子性</h3><blockquote>
<p>表中的每一列，在逻辑上都不可继续被分类</p>
</blockquote>
<table>
<thead>
<tr>
<th>student_no</th>
<th>Name</th>
<th>Grade</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>常昊</td>
<td>高三二班</td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</tbody></table>
<p>这个数据表明显不符合第一范式的原子性，因为Grade这一列中的高三二班，可以分为更细致的数据，高三年级，二班</p>
<h3 id="3-2-第二范式-唯一性"><a href="#3-2-第二范式-唯一性" class="headerlink" title="3.2 第二范式-唯一性"></a>3.2 第二范式-唯一性</h3><blockquote>
<p>数据表中的每条记录必须是唯一的,为了实现区分，通常给一列数据加上一个储存的唯一标示，这个唯一标示通常被叫为主键。</p>
</blockquote>
<table>
<thead>
<tr>
<th>name</th>
<th>age</th>
</tr>
</thead>
<tbody><tr>
<td>常昊</td>
<td>11</td>
</tr>
<tr>
<td>常昊</td>
<td>11</td>
</tr>
</tbody></table>
<p>一个学校有两个常昊年龄都是11岁，这样的数据出现在数据库就是不符合规范的，我们可以给每一个常昊价格唯一标示</p>
<table>
<thead>
<tr>
<th>name</th>
<th>age</th>
<th>id</th>
</tr>
</thead>
<tbody><tr>
<td>常昊</td>
<td>11</td>
<td>1</td>
</tr>
<tr>
<td>常昊</td>
<td>11</td>
<td>2</td>
</tr>
</tbody></table>
<p>这样就区分了一号常昊和二号常昊</p>
<h3 id="3-3-第三范式-关联性"><a href="#3-3-第三范式-关联性" class="headerlink" title="3.3 第三范式-关联性"></a>3.3 第三范式-关联性</h3><blockquote>
<p>每列都与主键有直接关系，不存在传递依赖</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201202180555.png" srcset="/img/loading.gif" alt="image-20201202180549587"></p>
<p>第一个表中,我们将爸爸这个当作主键，根据爸爸可以找到对应的子女，但是如果找到某个子女的物品，</p>
<p>就需要先去找到这个子女的爸爸，然后去拿出子女的物品，就比较慢，然后我们再设计的时候，父母-子女</p>
<p>子女-物品，这样就直接查询，速度会快很多。</p>
<blockquote>
<p>把数据拆分到不同的表，根据表中的主键，彼此关联</p>
</blockquote>
<p><strong>mysql字段约束</strong></p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201202181740.png" srcset="/img/loading.gif" alt="image-20201202181740301"></p>
<p>建议主键一定要用数字类型，mysql操作数字的速度，比操作字符串的速率快</p>
<p>前三种约束都很好理解</p>
<h4 id="我们主要看一下外键约束"><a href="#我们主要看一下外键约束" class="headerlink" title=" 我们主要看一下外键约束"></a><span style='color:red'> 我们主要看一下外键约束</span></h4><p> 我们准备设计teacher表和student表, 让student能根据一个外键找到他的老师。</p>
<p>我们先创建老师表，然后插入一条数据</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> teacher
<span class="token punctuation">(</span>
    id     <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    name   <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    tea_no <span class="token keyword">INT</span> <span class="token keyword">UNIQUE</span>   <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> teacher<span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> tea_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'赵太锋'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>tea_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>赵太锋</td>
<td>1</td>
</tr>
</tbody></table>
<p>然后创建学生表，</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> student
<span class="token punctuation">(</span>
    id     <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    name   <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    stu_no <span class="token keyword">INT</span> <span class="token keyword">UNIQUE</span>   <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    tea_no <span class="token keyword">INT</span>  <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>tea_no<span class="token punctuation">)</span> <span class="token keyword">references</span> teacher <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<blockquote>
<p>FOREIGN KEY (自己表中的字段) references teacher (引用其他的表的字段)</p>
</blockquote>
<p>然后插入一条学生数据</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> student<span class="token punctuation">(</span> name<span class="token punctuation">,</span> stu_no<span class="token punctuation">,</span> tea_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'常昊'</span><span class="token punctuation">,</span> <span class="token number">001</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>发现插入数据失败！</p>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">[2020-12-02 23:42:34] [23000][1452] Cannot add or update a child row: a foreign key constraint fails (&#96;ch&#96;.&#96;student&#96;, CONSTRAINT &#96;student_ibfk_1&#96; FOREIGN KEY (&#96;tea_no&#96;) REFERENCES &#96;teacher&#96; (&#96;id&#96;))
<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>这个意思其实就是，2这个插入的tea_no在teacher表中，没有找到引用id为2的数据，所以插入失败，这就是一个外键约束。</p>
<p>我们看到teacher只有一个id为1的老师，那我们重新更新一下插入学生的</p>
<p>sql语句</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> student<span class="token punctuation">(</span> name<span class="token punctuation">,</span> stu_no<span class="token punctuation">,</span> tea_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'常昊'</span><span class="token punctuation">,</span> <span class="token number">001</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>操作成功了，看一下我们的student表</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>stu_no</th>
<th>tea_no</th>
</tr>
</thead>
<tbody><tr>
<td>6</td>
<td>常昊</td>
<td>1</td>
<td><strong>1</strong></td>
</tr>
</tbody></table>
<h4 id="外键约束的闭环问题"><a href="#外键约束的闭环问题" class="headerlink" title="外键约束的闭环问题"></a><span style='color:red'>外键约束的闭环问题</span></h4><p><img src="http://qiniuyun.quancundexiwang.wang/20201202234954.png" srcset="/img/loading.gif" alt="image-20201202234954375"></p>
<h2 id="4-数据库的索引机制"><a href="#4-数据库的索引机制" class="headerlink" title="4.数据库的索引机制"></a>4.数据库的索引机制</h2><blockquote>
<p>就像查字典一样，要查到 change这个单词 根据c字母，去更小范围查找，就比直接一个一个找快很多。</p>
</blockquote>
<h3 id="4-1-普通索引"><a href="#4-1-普通索引" class="headerlink" title="4.1 普通索引"></a>4.1 普通索引</h3><h4 id="创建索引"><a href="#创建索引" class="headerlink" title="创建索引"></a>创建索引</h4><p>这是最基本的索引，它没有任何限制。它有以下几种创建方式：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> indexNa	me <span class="token keyword">ON</span> table_name <span class="token punctuation">(</span>column_name<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。</p>
<h4 id="修改表结构-添加索引"><a href="#修改表结构-添加索引" class="headerlink" title="修改表结构(添加索引)"></a>修改表结构(添加索引)</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">table</span> tableName <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> indexName<span class="token punctuation">(</span>columnName<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h4 id="创建表的时候直接指定"><a href="#创建表的时候直接指定" class="headerlink" title="创建表的时候直接指定"></a>创建表的时候直接指定</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mytable<span class="token punctuation">(</span>  
 
ID <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>   
 
username <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>  
 
<span class="token keyword">INDEX</span> <span class="token punctuation">[</span>indexName<span class="token punctuation">]</span> <span class="token punctuation">(</span>username<span class="token punctuation">(</span>length<span class="token punctuation">)</span><span class="token punctuation">)</span>  
 
<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<h4 id="删除索引的语法"><a href="#删除索引的语法" class="headerlink" title="删除索引的语法"></a>删除索引的语法</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token punctuation">[</span>indexName<span class="token punctuation">]</span> <span class="token keyword">ON</span> mytable<span class="token punctuation">;</span> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h4 id="查看索引的语法格式如下："><a href="#查看索引的语法格式如下：" class="headerlink" title="查看索引的语法格式如下："></a>查看索引的语法格式如下：</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> tableName <span class="token punctuation">[</span> <span class="token keyword">FROM</span> <span class="token operator">&lt;</span>数据库名<span class="token operator">></span><span class="token punctuation">]</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h3 id="4-2-唯一索引"><a href="#4-2-唯一索引" class="headerlink" title="4.2 唯一索引"></a>4.2 唯一索引</h3><blockquote>
<p>它与前面的普通索引类似，不同的就是<strong>：<code>索引列的值必须唯一</code>**，</strong>但<code>允许有空值</code><strong>。如果</strong>是<code>组合索引</code><strong>，则</strong>列值的组合必须唯一**。它有以下几种创建方式：</p>
</blockquote>
<p>关于 <strong><code>组合索引</code></strong></p>
<p>我们先创建一个表，</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test1
<span class="token punctuation">(</span>
    id   <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>                   <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    age  <span class="token keyword">INT</span>                            <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span>
<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>Field</th>
<th>Type</th>
<th>Null</th>
<th>Key</th>
<th>Default</th>
<th>Extra</th>
</tr>
</thead>
<tbody><tr>
<td>id</td>
<td>int(11)</td>
<td>NO</td>
<td>PRI</td>
<td>NULL</td>
<td>auto_increment</td>
</tr>
<tr>
<td>name</td>
<td>varchar(100)</td>
<td>NO</td>
<td></td>
<td>NULL</td>
<td></td>
</tr>
<tr>
<td>age</td>
<td>int(11)</td>
<td>NO</td>
<td></td>
<td>0</td>
<td></td>
</tr>
</tbody></table>
<p>然后给这个表添加组合索引，也就是多个列组成一个索引，这个组合索引必须是唯一的，我们在这里要求，name+age的组合必须是唯一的</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> idx_na <span class="token keyword">ON</span> test1 <span class="token punctuation">(</span>name<span class="token punctuation">,</span> age<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> <span class="token keyword">INDEX</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>Table</th>
<th>Non_unique</th>
<th>Key_name</th>
<th>Seq_in_index</th>
<th>Column_name</th>
<th>Collation</th>
<th>Cardinality</th>
<th>Sub_part</th>
<th>Packed</th>
<th>Null</th>
<th>Index_type</th>
<th>Comment</th>
<th>Index_comment</th>
</tr>
</thead>
<tbody><tr>
<td>test1</td>
<td>0</td>
<td>PRIMARY</td>
<td>1</td>
<td>id</td>
<td>A</td>
<td>0</td>
<td>NULL</td>
<td>NULL</td>
<td></td>
<td>BTREE</td>
<td></td>
<td></td>
</tr>
<tr>
<td>test1</td>
<td>0</td>
<td>idx_na</td>
<td>1</td>
<td>name</td>
<td>A</td>
<td>0</td>
<td>NULL</td>
<td>NULL</td>
<td></td>
<td>BTREE</td>
<td></td>
<td></td>
</tr>
<tr>
<td>test1</td>
<td>0</td>
<td>idx_na</td>
<td>2</td>
<td>age</td>
<td>A</td>
<td>0</td>
<td>NULL</td>
<td>NULL</td>
<td></td>
<td>BTREE</td>
<td></td>
<td></td>
</tr>
</tbody></table>
<p>然后插入一个数据</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1<span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> age<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'哈哈'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>age</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>哈哈</td>
<td>1</td>
</tr>
</tbody></table>
<p>因为主键也是一个索引,name-ag组合也不能重复。</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> test1<span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">,</span> age<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'哈哈'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token punctuation">[</span><span class="token number">2020</span><span class="token operator">-</span><span class="token number">12</span><span class="token operator">-</span><span class="token number">03</span> <span class="token number">07</span>:<span class="token number">56</span>:<span class="token number">58</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token number">23000</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">1062</span><span class="token punctuation">]</span> <span class="token keyword">Duplicate</span> entry <span class="token string">'哈哈-1'</span> <span class="token keyword">for</span> <span class="token keyword">key</span> <span class="token string">'idx_na'</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> test1<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<h4 id="创建索引-1"><a href="#创建索引-1" class="headerlink" title="创建索引"></a>创建索引</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> indexName <span class="token keyword">ON</span> mytable<span class="token punctuation">(</span>username<span class="token punctuation">(</span>length<span class="token punctuation">)</span><span class="token punctuation">)</span> <span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h4 id="修改表结构"><a href="#修改表结构" class="headerlink" title="修改表结构"></a>修改表结构</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">table</span> mytable <span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span> <span class="token punctuation">[</span>indexName<span class="token punctuation">]</span> <span class="token punctuation">(</span>username<span class="token punctuation">(</span>length<span class="token punctuation">)</span><span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h4 id="创建表的时候直接指定-1"><a href="#创建表的时候直接指定-1" class="headerlink" title="创建表的时候直接指定"></a>创建表的时候直接指定</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> mytable<span class="token punctuation">(</span>  
 
ID <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>   
 
username <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>  
 
<span class="token keyword">UNIQUE</span> <span class="token punctuation">[</span>indexName<span class="token punctuation">]</span> <span class="token punctuation">(</span>username<span class="token punctuation">(</span>length<span class="token punctuation">)</span><span class="token punctuation">)</span>  
 
<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<h4 id="使用ALTER-命令添加和删除索引"><a href="#使用ALTER-命令添加和删除索引" class="headerlink" title="使用ALTER 命令添加和删除索引"></a>使用ALTER 命令添加和删除索引</h4><p>有四种方式来添加数据表的索引：</p>
<ul>
<li><p><strong><code>ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)</code>:</strong></p>
<p>该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。</p>
</li>
</ul>
<ul>
<li><p><strong>ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):</strong> 这条语句创建索引的值必须是唯一的（<strong>除了NULL外，NULL可能会出现多次）。</strong></p>
</li>
<li><p><strong>ALTER TABLE tbl_name ADD INDEX index_name (column_list):</strong> 添加普通索引，索引值可出现多次。</p>
</li>
<li><p>**ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):**该语句指定了索引为 FULLTEXT ，用于全文索引。</p>
</li>
</ul>
<h4 id="使用-ALTER-命令添加和删除主键"><a href="#使用-ALTER-命令添加和删除主键" class="headerlink" title="使用 ALTER 命令添加和删除主键"></a>使用 ALTER 命令添加和删除主键</h4><p>主键作用于列上（可以<strong>一个列或多个列联合主键</strong>），添加主键索引时，你需要确保该<strong>主键默认不为空</strong>（NOT NULL）。实例如下：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> testalter_tbl <span class="token keyword">MODIFY</span> i <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span> <span class="token comment"># 修改i为数字类型，不能为空</span>
mysql<span class="token operator">></span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> testalter_tbl <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>i<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token comment"># 设置i为主键</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>你也可以使用 ALTER 命令删除主键：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> testalter_tbl <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h2 id="5-数据表的基本查询"><a href="#5-数据表的基本查询" class="headerlink" title="5.数据表的基本查询"></a>5.数据表的基本查询</h2><h3 id="5-1-查找表中指定列的数据"><a href="#5-1-查找表中指定列的数据" class="headerlink" title="5.1 查找表中指定列的数据"></a>5.1 查找表中指定列的数据</h3><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span> <span class="token comment"># 默认查找全部字段</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h2 id=""><a href="#" class="headerlink" title=""></a><img src="http://qiniuyun.quancundexiwang.wang/20201203081849.png" srcset="/img/loading.gif" alt="image-20201203081844500"></h2><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> cloumnName1<span class="token punctuation">,</span> cloumnName2<span class="token punctuation">,</span> cloumnName3<span class="token punctuation">,</span> <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span> <span class="token comment"># 根据列某些字段查找</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> typeName<span class="token punctuation">,</span>remark <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>typeName</th>
<th>remark</th>
</tr>
</thead>
<tbody><tr>
<td>js基础巩固</td>
<td>remark</td>
</tr>
<tr>
<td>Mysql</td>
<td>remark</td>
</tr>
<tr>
<td>测试用户专用标签！</td>
<td>记录vue前端框架学习历程</td>
</tr>
<tr>
<td>Vue</td>
<td>这是一个标签</td>
</tr>
<tr>
<td>webpackStength</td>
<td>从头到位系统梳理一下webpack的知识！</td>
</tr>
<tr>
<td>小知识点</td>
<td>小知识点-面试-学习！</td>
</tr>
<tr>
<td>99</td>
<td>99</td>
</tr>
</tbody></table>
<h3 id="5-2-查询语句表达式与别名"><a href="#5-2-查询语句表达式与别名" class="headerlink" title="5.2 查询语句表达式与别名"></a>5.2 查询语句表达式与别名</h3><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> typeName<span class="token punctuation">,</span> <span class="token keyword">status</span> <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>typeName</th>
<th>status</th>
</tr>
</thead>
<tbody><tr>
<td>js基础巩固</td>
<td>1</td>
</tr>
<tr>
<td>Mysql</td>
<td>1</td>
</tr>
<tr>
<td>测试用户专用标签！</td>
<td>1</td>
</tr>
<tr>
<td>Vue</td>
<td>1</td>
</tr>
<tr>
<td>webpackStength</td>
<td>1</td>
</tr>
<tr>
<td>小知识点</td>
<td>1</td>
</tr>
<tr>
<td>99</td>
<td>99</td>
</tr>
</tbody></table>
<p>​    </p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> typeName<span class="token punctuation">,</span> <span class="token keyword">status</span> <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>typeName</th>
<th>status + 1</th>
</tr>
</thead>
<tbody><tr>
<td>js基础巩固</td>
<td>2</td>
</tr>
<tr>
<td>Mysql</td>
<td>2</td>
</tr>
<tr>
<td>测试用户专用标签！</td>
<td>2</td>
</tr>
<tr>
<td>Vue</td>
<td>2</td>
</tr>
<tr>
<td>webpackStength</td>
<td>2</td>
</tr>
<tr>
<td>小知识点</td>
<td>2</td>
</tr>
<tr>
<td>99</td>
<td>100</td>
</tr>
</tbody></table>
<p>上面我们使用了表达式对status进行操作，但是发现表头变成了表达式，不利于输出数据的可读性，这里我们可以使用as 语句，</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> typeName<span class="token punctuation">,</span> <span class="token keyword">status</span> <span class="token operator">+</span> <span class="token number">1</span> <span class="token keyword">AS</span> _status <span class="token keyword">FROM</span> <span class="token keyword">type</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>typeName</th>
<th>_status</th>
</tr>
</thead>
<tbody><tr>
<td>js基础巩固</td>
<td>2</td>
</tr>
<tr>
<td>Mysql</td>
<td>2</td>
</tr>
<tr>
<td>测试用户专用标签！</td>
<td>2</td>
</tr>
<tr>
<td>Vue</td>
<td>2</td>
</tr>
<tr>
<td>webpackStength</td>
<td>2</td>
</tr>
<tr>
<td>小知识点</td>
<td>2</td>
</tr>
<tr>
<td>99</td>
<td>100</td>
</tr>
</tbody></table>
<h3 id="5-3-分页查询表中的数据-limit关键字"><a href="#5-3-分页查询表中的数据-limit关键字" class="headerlink" title="5.3 分页查询表中的数据- limit关键字"></a>5.3 分页查询表中的数据- limit关键字</h3><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> essay<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<blockquote>
<p>默认情况下查找，所有的数据，但是如果数据很多，浪费cpu和网络资源，我们就可以把数据，分成一部分一部分的输出给用户。</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201203084108.png" srcset="/img/loading.gif" alt="image-20201203084108720"></p>
<p>可以看到essay表总共35条数据，使用limit关键字可以指定起始位置，和数据偏移量</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> essay <span class="token keyword">limit</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span> <span class="token comment">#只看essay表中的前十条数据</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201203084255.png" srcset="/img/loading.gif" alt="image-20201203084255823"></p>
<h3 id="5-4-对数据表的查询结果进行排序"><a href="#5-4-对数据表的查询结果进行排序" class="headerlink" title="5.4 对数据表的查询结果进行排序"></a>5.4 对数据表的查询结果进行排序</h3><blockquote>
<p>查询出的数据默认根据主键排序，当然我们可以根据我们的需求指定单个或者多个字段进行排序</p>
</blockquote>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> essay
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> type_id <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201203084532.png" srcset="/img/loading.gif" alt="image-20201203084532320"></p>
<p>当指定单个的排序字段，如果里面有重复的，则根据主键排序，如果指定多个字段排序，有重复的会默认使用下一个指定的字段进行排序，继续重复，以此类推，直到使用主键排序。</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> updatedAt<span class="token punctuation">,</span> title<span class="token punctuation">,</span> type_id <span class="token keyword">FROM</span> essay
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> type_id <span class="token punctuation">,</span> updatedAt <span class="token comment">#根据type_id和updatedAt排序</span>
<span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>updatedAt</th>
<th>title</th>
<th>type_id</th>
</tr>
</thead>
<tbody><tr>
<td>2020-05-10 19:51:03</td>
<td>{}+{}结果的长度！</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-11 10:58:52</td>
<td>JS call/apply复习！</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-15 07:33:32</td>
<td>JS预编译过程</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-17 15:12:52</td>
<td>复习-防抖</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-10 17:22:19</td>
<td>MySql-记录-文章表和类型表的关联</td>
<td>2</td>
</tr>
<tr>
<td>2020-05-11 18:31:41</td>
<td>Mysql数据的导入和导出</td>
<td>2</td>
</tr>
<tr>
<td>2020-05-09 20:08:46</td>
<td>哈哈哈</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-12 22:58:57</td>
<td>测试用户留言~~</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-12 23:00:15</td>
<td>胡哈哈哈哈老哥啊</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-10 17:18:04</td>
<td>组件之间传值</td>
<td>4</td>
</tr>
</tbody></table>
<p>当type_id相同的时候，就使用updateAt进行默认排序，</p>
<p>排序默认是升序的，我们可以指定字段的排序是升序，还是降序（DESC）</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> updatedAt<span class="token punctuation">,</span> title<span class="token punctuation">,</span> type_id
<span class="token keyword">FROM</span> essay
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> type_id<span class="token punctuation">,</span> updatedAt <span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>updatedAt</th>
<th>title</th>
<th>type_id</th>
</tr>
</thead>
<tbody><tr>
<td>2020-05-17 15:12:52</td>
<td>复习-防抖</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-15 07:33:32</td>
<td>JS预编译过程</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-11 10:58:52</td>
<td>JS call/apply复习！</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-10 19:51:03</td>
<td>{}+{}结果的长度！</td>
<td>1</td>
</tr>
<tr>
<td>2020-05-11 18:31:41</td>
<td>Mysql数据的导入和导出</td>
<td>2</td>
</tr>
<tr>
<td>2020-05-10 17:22:19</td>
<td>MySql-记录-文章表和类型表的关联</td>
<td>2</td>
</tr>
<tr>
<td>2020-05-12 23:00:15</td>
<td>胡哈哈哈哈老哥啊</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-12 22:58:57</td>
<td>测试用户留言~~</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-09 20:08:46</td>
<td>哈哈哈</td>
<td>3</td>
</tr>
<tr>
<td>2020-05-17 11:00:37</td>
<td>vue-v-model语法糖背后的故事</td>
<td>4</td>
</tr>
</tbody></table>
<p>这里我们可以发现， type_id 默认安按照升序排列，当type_id相等的时候，根据updatedAt降序排列。</p>
<h3 id="5-5-去除结果集中的重复记录-distinct"><a href="#5-5-去除结果集中的重复记录-distinct" class="headerlink" title="5.5 去除结果集中的重复记录-distinct"></a>5.5 去除结果集中的重复记录-distinct</h3><p>如果我们取数据的时候，不拿主键字段，就可能造成去除的数据是重复的</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> type_id
<span class="token keyword">FROM</span> essay
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> type_id
<span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>type_id</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
</tr>
<tr>
<td>1</td>
</tr>
<tr>
<td>1</td>
</tr>
<tr>
<td>1</td>
</tr>
<tr>
<td>2</td>
</tr>
<tr>
<td>2</td>
</tr>
<tr>
<td>3</td>
</tr>
<tr>
<td>3</td>
</tr>
<tr>
<td>3</td>
</tr>
<tr>
<td>4</td>
</tr>
</tbody></table>
<p>我们想去除结果中的重复的值</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">distinct</span> type_id
<span class="token keyword">FROM</span> essay
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> type_id
<span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">,</span><span class="token number">10</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>type_id</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
</tr>
<tr>
<td>2</td>
</tr>
<tr>
<td>3</td>
</tr>
<tr>
<td>4</td>
</tr>
<tr>
<td>5</td>
</tr>
<tr>
<td>6</td>
</tr>
</tbody></table>
<h2 id="6-数据表的高级查询"><a href="#6-数据表的高级查询" class="headerlink" title="6.数据表的高级查询"></a>6.数据表的高级查询</h2><h3 id="6-1-聚合函数"><a href="#6-1-聚合函数" class="headerlink" title="6.1 聚合函数"></a>6.1 聚合函数</h3><blockquote>
<p>聚合函数对数据进行统计的一些函数(求和，最大值，最小值，平均值等等)，这些只能做全表范围的统计。</p>
</blockquote>
<h4 id="6-1-1-常用的聚合函数"><a href="#6-1-1-常用的聚合函数" class="headerlink" title="6.1.1 常用的聚合函数"></a>6.1.1 常用的聚合函数</h4><ul>
<li><p>AVG(平均值)</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">USE</span> CH<span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> employee
<span class="token punctuation">(</span>
    id     <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    name   <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>                   <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    salary <span class="token keyword">INT</span>                            <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee<span class="token punctuation">(</span>name<span class="token punctuation">,</span> salary<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'张三'</span><span class="token punctuation">,</span> <span class="token number">9000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee<span class="token punctuation">(</span>name<span class="token punctuation">,</span> salary<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'李四'</span><span class="token punctuation">,</span> <span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee<span class="token punctuation">(</span>name<span class="token punctuation">,</span> salary<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'王二'</span><span class="token punctuation">,</span> <span class="token number">11000</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>​    我们先创建一个员工employee表，插入三条员工的数据</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>10000</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
</tr>
</tbody></table>
<p>我们使用平均数的聚合函数来计算他们工资的平均值</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> employee<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>​    <img src="http://qiniuyun.quancundexiwang.wang/20201203232716.png" srcset="/img/loading.gif" alt="image-20201203232716292"></p>
<p>这样就计算出了所选字段的平均值，我们如果想让读出的,字段好看一点，可以使用<code>AS</code>关键字</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> salary_avg
<span class="token keyword">FROM</span> employee <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p> <img src="http://qiniuyun.quancundexiwang.wang/20201203233110.png" srcset="/img/loading.gif" alt="image-20201203233110327"></p>
</li>
<li><p>SUM 对数字类型进行求和（对字符串求和返回0，日期求和毫秒数相加）</p>
  <div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> salary_total
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201203233500.png" srcset="/img/loading.gif" alt="image-20201203233500127"></p>
  <div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> salary_total
<span class="token keyword">FROM</span> employee <span class="token keyword">WHERE</span> id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201203233601.png" srcset="/img/loading.gif" alt="image-20201203233601426"></p>
</li>
<li><p>MAX 选出最大值</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> max_salary
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>


</li>
</ul>
<div class="code-wrapper"><pre><code>   ![image-20201203233847294](http://qiniuyun.quancundexiwang.wang/20201203233847.png)</code></pre></div>
<ul>
<li><p>MIN 选出最小值</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> min_salary
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>​    <img src="http://qiniuyun.quancundexiwang.wang/20201203234019.png" srcset="/img/loading.gif" alt="image-20201203234019365"></p>
</li>
</ul>
<ul>
<li><p>COUNT 记录某个字段记录值的次数</p>
<p>我们新插入一个字段</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employee
    <span class="token keyword">ADD</span> hobby <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>​    此时的表中的数据是这样的：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>hobby</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>NULL</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>10000</td>
<td>NULL</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>NULL</td>
</tr>
</tbody></table>
  <div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>hobby<span class="token punctuation">)</span> <span class="token keyword">as</span> record_num_on_hobby
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>  <strong>统计指定字段有效（not null）的数据条数</strong></p>
<p>  <img src="http://qiniuyun.quancundexiwang.wang/20201203235318.png" srcset="/img/loading.gif" alt="image-20201203235318466"></p>
  <div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">as</span> record_num_on_table
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<p>  <strong>统计表中所有数据的条数，不管有效还是无效的</strong></p>
<p>  <img src="http://qiniuyun.quancundexiwang.wang/20201203235343.png" srcset="/img/loading.gif" alt="image-20201203235343814"></p>
</li>
</ul>
<ul>
<li><p>配合where语句查询</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>hobby</th>
<th>join_date</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>NULL</td>
<td>1994-05-06</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>10000</td>
<td>NULL</td>
<td>1996-05-16</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>NULL</td>
<td>1997-05-20</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>NULL</td>
<td>2000-05-12</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>NULL</td>
<td>2001-12-19</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>NULL</td>
<td>2004-05-29</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">where</span> join_date <span class="token operator">>=</span> <span class="token string">'2000-01-01'</span> <span class="token operator">AND</span> salary <span class="token operator">></span> <span class="token number">10000</span>
<span class="token comment"># 找到加入时间在2000以后并且工资大于10000的</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201204001026.png" srcset="/img/loading.gif" alt="image-20201204001026429"></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">where</span> join_date <span class="token operator">>=</span> <span class="token string">'2000-01-01'</span> <span class="token operator">AND</span> salary <span class="token operator">></span> <span class="token number">10000</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>hobby</th>
<th>join_date</th>
</tr>
</thead>
<tbody><tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>NULL</td>
<td>2000-05-12</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>NULL</td>
<td>2001-12-19</td>
</tr>
</tbody></table>
</li>
</ul>
<p>可以看到的确就两条数据。</p>
<p>​    </p>
<h4 id="WHERE-语句中不能放入聚合函数"><a href="#WHERE-语句中不能放入聚合函数" class="headerlink" title="  WHERE 语句中不能放入聚合函数 "></a><span style='color:red'>  WHERE 语句中不能放入聚合函数 </span></h4><p>查找 2000年加入，而且平均低于超出平均工资的人</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">WHERE</span> join_date <span class="token operator">>=</span> <span class="token string">'2000-01-01'</span>
<span class="token operator">AND</span> salary <span class="token operator">></span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">;</span>  <span class="token comment"># 这种语句是错误的。</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><span style='color:red'>因为聚合函数要操作的数据，是从你where语句中筛选出来的，你只有筛选出了那些数据需要统计的，才能使用聚合函数去执行统计，所以，where语句中不能使用聚合函数。</span></p>
<p>那么对于这种条件，我们可以使用后面学到的分组查询进行查找</p>
<h3 id="6-2-分组查询"><a href="#6-2-分组查询" class="headerlink" title="6.2 分组查询"></a>6.2 分组查询</h3><h4 id="6-2-1-基本分组查询"><a href="#6-2-1-基本分组查询" class="headerlink" title="6.2.1 基本分组查询"></a>6.2.1 基本分组查询</h4><blockquote>
<p>分组查询：当我们需要对表内数据进行分条件查询数据的话，就需要group by ，HAVING语句进行统计。</p>
</blockquote>
<blockquote>
<p>为什么要分组：默认情况下聚合汇总函数是对全表进行统计，分组是根据某种规则，把整个表的数据，规划成一个一个的分组，再对每一个区域的分组数据，进行汇总统计。</p>
</blockquote>
<p> <img src="http://qiniuyun.quancundexiwang.wang/20201204120249.png" srcset="/img/loading.gif" alt="image-20201204120249683"></p>
<p>​    需求：计算work工作为捧哏和逗哏两种位置的平均工资。</p>
<p>​    先把表中根据工作相同的分组，然后计算平均工资</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token function">AVG</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> salary_avg <span class="token keyword">FROM</span> employee <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201204145408.png" srcset="/img/loading.gif" alt="image-20201204145407981"></p>
<h4 id="6-2-2-逐级分组查询"><a href="#6-2-2-逐级分组查询" class="headerlink" title="6.2.2 逐级分组查询"></a>6.2.2 <strong>逐级分组查询</strong></h4><p>我们再给employee添加一个等级，一级相声演员和二级相声演员，</p>
<p>需求，查询<code>一级相声演员的捧哏和逗哏的平均工资</code>和<code>二级相声演员的捧哏和逗哏的平均工资</code>。</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201204152118.png" srcset="/img/loading.gif" alt="image-20201204152117919"></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span><span class="token keyword">level</span><span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token function">AVG</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span>salary<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> salary_avg <span class="token keyword">FROM</span> employee <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span><span class="token keyword">level</span><span class="token punctuation">`</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>​    <img src="http://qiniuyun.quancundexiwang.wang/20201204152215.png" srcset="/img/loading.gif" alt="image-20201204152215433"></p>
<p>首先执行from挑选范围，employee表根据work,level分组，然后计算平均工资。</p>
<h4 id="6-2-3-WITH-ROLLUP关键词"><a href="#6-2-3-WITH-ROLLUP关键词" class="headerlink" title="6.2.3 WITH ROLLUP关键词"></a>6.2.3 <code>WITH ROLLUP</code>关键词</h4><blockquote>
<p>对汇总结果再次进行统计</p>
</blockquote>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span>
   <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span><span class="token punctuation">,</span>
   <span class="token punctuation">`</span><span class="token keyword">level</span><span class="token punctuation">`</span><span class="token punctuation">,</span>
   <span class="token function">SUM</span><span class="token punctuation">(</span>
   IFNULL<span class="token punctuation">(</span> salary<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'工资总数'</span><span class="token punctuation">,</span>
   <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'人数'</span><span class="token punctuation">,</span>
   <span class="token function">AVG</span><span class="token punctuation">(</span>
   IFNULL<span class="token punctuation">(</span> salary<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'工资平均数'</span><span class="token punctuation">,</span>
   <span class="token function">MAX</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span> salary<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'最高工资'</span><span class="token punctuation">,</span>
   <span class="token function">MIN</span><span class="token punctuation">(</span>IFNULL<span class="token punctuation">(</span> salary<span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'最低工资'</span>
<span class="token keyword">FROM</span>
   employee
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
   <span class="token punctuation">`</span><span class="token keyword">work</span><span class="token punctuation">`</span><span class="token punctuation">,</span>
   <span class="token punctuation">`</span><span class="token keyword">level</span><span class="token punctuation">`</span> <span class="token keyword">WITH ROLLUP</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>work</th>
<th>level</th>
<th>工资总数</th>
<th>人数</th>
<th>工资平均数</th>
<th>最高工资</th>
<th>最低工资</th>
</tr>
</thead>
<tbody><tr>
<td>捧哏</td>
<td>1</td>
<td>13000</td>
<td>1</td>
<td>13000.0000</td>
<td>13000</td>
<td>13000</td>
</tr>
<tr>
<td>捧哏</td>
<td>2</td>
<td>6000</td>
<td>1</td>
<td>6000.0000</td>
<td>6000</td>
<td>6000</td>
</tr>
<tr>
<td>捧哏</td>
<td>NULL</td>
<td>19000</td>
<td>2</td>
<td>9500.0000</td>
<td>13000</td>
<td>6000</td>
</tr>
<tr>
<td>逗哏</td>
<td>1</td>
<td>27000</td>
<td>2</td>
<td>13500.0000</td>
<td>18000</td>
<td>9000</td>
</tr>
<tr>
<td>逗哏</td>
<td>2</td>
<td>22000</td>
<td>3</td>
<td>7333.3333</td>
<td>11000</td>
<td>3000</td>
</tr>
<tr>
<td>逗哏</td>
<td>NULL</td>
<td>49000</td>
<td>5</td>
<td>9800.0000</td>
<td>18000</td>
<td>3000</td>
</tr>
<tr>
<td>NULL</td>
<td>NULL</td>
<td>68000</td>
<td>7</td>
<td>9714.2857</td>
<td>18000</td>
<td>3000</td>
</tr>
</tbody></table>
<h4 id="6-2-4-GROUP-CONCAT-关键词"><a href="#6-2-4-GROUP-CONCAT-关键词" class="headerlink" title="6.2.4 GROUP CONCAT   关键词"></a>6.2.4 <code>GROUP CONCAT</code>   关键词</h4><table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
</tr>
</tbody></table>
<p>上面这张表，如果你想看捧哏和逗哏都有哪些人的话，</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">work</span><span class="token punctuation">,</span>name <span class="token keyword">FROM</span> employee <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">work</span><span class="token punctuation">;</span>

<span class="token comment"># 这样的SQL语句是错误的，因为work分组后的记录和name条数可能不相同，mysql不能很好的解析整理数据，这个时候就用到我们的GROUP CONCAT`   关键词了</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-none"><code class="language-none">SELECT work, GROUP_CONCAT(name)
FROM employee
GROUP BY work;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>work</th>
<th>GROUP_CONCAT(name)</th>
</tr>
</thead>
<tbody><tr>
<td>捧哏</td>
<td>李四,于谦</td>
</tr>
<tr>
<td>逗哏</td>
<td>张三,王二,郭德纲,岳云鹏,二妞</td>
</tr>
</tbody></table>
<p>GROUP CONTACT 就是让可能分组后需要查询出字段记录条数不一样的拼接在一起。</p>
<h4 id="6-2-5-查询语句的执行顺序"><a href="#6-2-5-查询语句的执行顺序" class="headerlink" title="6.2.5 查询语句的执行顺序"></a><span style='color:red'>6.2.5 查询语句的执行顺序</span></h4><ul>
<li>FROM 先执行，确定取表的数据</li>
<li>WHERE 取挑选符合筛选条件的数据留下</li>
<li>GROUP BY 分组由上一步 筛选出的数据</li>
<li>SELECT 挑选要查询展示的字段</li>
<li>ORDER BY 对数据进行排序</li>
<li>LIMIT 限制输出的数据长度</li>
</ul>
<h4 id="6-2-6-HAVING子句"><a href="#6-2-6-HAVING子句" class="headerlink" title="6.2.6 HAVING子句"></a>6.2.6 HAVING子句</h4><blockquote>
<p>HAVING 子句需要和GROUP BY 自己结合使用 </p>
</blockquote>
 <div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> CH<span class="token punctuation">.</span>employee <span class="token punctuation">(</span>name<span class="token punctuation">,</span> salary<span class="token punctuation">,</span> <span class="token keyword">work</span><span class="token punctuation">,</span> join_date<span class="token punctuation">,</span> <span class="token keyword">level</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'王平'</span><span class="token punctuation">,</span> <span class="token number">5000</span><span class="token punctuation">,</span> <span class="token string">'报幕'</span><span class="token punctuation">,</span> <span class="token string">'2020-12-08'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'二傻'</span><span class="token punctuation">,</span> <span class="token number">400</span><span class="token punctuation">,</span> <span class="token string">'报幕'</span><span class="token punctuation">,</span> <span class="token string">'2020-12-10'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'少侯爷'</span><span class="token punctuation">,</span> <span class="token number">3000</span><span class="token punctuation">,</span> <span class="token string">'报幕'</span><span class="token punctuation">,</span> <span class="token string">'1993-12-09'</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>3000</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
</tr>
</tbody></table>
<p>需求：查询人数大于3人的work，然后work的平均工资大于4000</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">work</span><span class="token punctuation">,</span> <span class="token function">avg</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token string">'平均工资'</span><span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'人数'</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token keyword">work</span>
<span class="token keyword">HAVING</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">>=</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token operator">></span><span class="token number">4000</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>work</th>
<th>平均工资</th>
<th>人数</th>
</tr>
</thead>
<tbody><tr>
<td>逗哏</td>
<td>9800.0000</td>
<td>5</td>
</tr>
</tbody></table>
<h4 id="6-2-7-HAVING子句特殊用法"><a href="#6-2-7-HAVING子句特殊用法" class="headerlink" title="6.2.7 HAVING子句特殊用法"></a>6.2.7 HAVING子句特殊用法</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">work</span><span class="token punctuation">,</span> <span class="token function">avg</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token string">'平均工资'</span><span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'人数'</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token number">1</span>   <span class="token comment"># 这个1就代表SELECT 关键词 选择的列的字段的位置，这里就对应work， </span>
<span class="token keyword">HAVING</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">>=</span> <span class="token number">3</span> <span class="token operator">AND</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token operator">></span><span class="token number">4000</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>


<h3 id="6-3-表连接查询-内连接和外连接查询"><a href="#6-3-表连接查询-内连接和外连接查询" class="headerlink" title="6.3 表连接查询(内连接和外连接查询)"></a>6.3 表连接查询(内连接和外连接查询)</h3><h4 id="6-3-1-初次尝试"><a href="#6-3-1-初次尝试" class="headerlink" title="6.3.1 初次尝试"></a>6.3.1 初次尝试</h4><blockquote>
<p>表连接查询，就是从多张表中提取数据，所以表和表之间必须有关联条件字段，比如一个相声演员，应该是属于某一个小剧场的演员。</p>
<p>如果不定义关联条件，就会出现无条件连接，两张表数据会产生交叉连接，产生笛卡尔积。</p>
</blockquote>
<p>​       创建新的相声社团表</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> associations
<span class="token punctuation">(</span>
    id       <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    ass_name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token keyword">UNIQUE</span>            <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> associations
    <span class="token keyword">ADD</span> ass_no <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> associations <span class="token punctuation">(</span>ass_name<span class="token punctuation">,</span> ass_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'北京德云色'</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'辽宁德云色'</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'天津德云色'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> associations<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employee
    <span class="token keyword">ADD</span> ass_no <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>手动添加数据</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
<td>10</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>3000</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
<td>30</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>name <span class="token keyword">as</span> <span class="token string">'姓名'</span><span class="token punctuation">,</span> e<span class="token punctuation">.</span>salary <span class="token keyword">as</span> <span class="token string">'工资'</span><span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name <span class="token keyword">as</span> <span class="token string">'社团'</span>
<span class="token comment"># 2.可选表范围内的使用</span>
<span class="token keyword">FROM</span> employee e <span class="token keyword">join</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span> 
<span class="token comment"># 1.选择范围是employee表 as e ，associations as a,使用join将两张表连接起来，on来打通表关系字段。</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>姓名</th>
<th>工资</th>
<th>社团</th>
</tr>
</thead>
<tbody><tr>
<td>张三</td>
<td>9000</td>
<td>北京德云色</td>
</tr>
<tr>
<td>李四</td>
<td>6000</td>
<td>北京德云色</td>
</tr>
<tr>
<td>王二</td>
<td>11000</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>郭德纲</td>
<td>18000</td>
<td>北京德云色</td>
</tr>
<tr>
<td>于谦</td>
<td>13000</td>
<td>天津德云色</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>8000</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>二妞</td>
<td>3000</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>王平</td>
<td>5000</td>
<td>天津德云色</td>
</tr>
<tr>
<td>二傻</td>
<td>4000</td>
<td>天津德云色</td>
</tr>
<tr>
<td>少侯爷</td>
<td>3000</td>
<td>天津德云色</td>
</tr>
</tbody></table>
<h4 id="6-3-2-表的内连接-JOIN"><a href="#6-3-2-表的内连接-JOIN" class="headerlink" title="6.3.2 表的内连接-JOIN"></a>6.3.2 表的内连接-JOIN</h4><blockquote>
<p>内连接，是指结果集中只保留符合连接条件的记录。</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201206000124.png" srcset="/img/loading.gif" alt="image-20201206000124054"></p>
<p>内连接的多种语法形式</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201206000206.png" srcset="/img/loading.gif" alt="image-20201206000206791"></p>
<p>新增一张工资等级表</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> salary_level
<span class="token punctuation">(</span>
    id         <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    level_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>                   <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    MAX        <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">,</span>
    MIN        <span class="token keyword">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-none"><code class="language-none">INSERT INTO salary_level(LEVEL_NAME, MAX, MIN)
VALUES (&#39;高收入&#39;, 100000, 20000),
       (&#39;中等收入&#39;, 20000, 10000),
       (&#39;低收入&#39;, 10000, 5000),
       (&#39;贫困&#39;, 10000, 5000);<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>level_name</th>
<th>MAX</th>
<th>MIN</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>高收入</td>
<td>100000</td>
<td>20000</td>
</tr>
<tr>
<td>2</td>
<td>中等收入</td>
<td>20000</td>
<td>10000</td>
</tr>
<tr>
<td>3</td>
<td>低收入</td>
<td>10000</td>
<td>5000</td>
</tr>
<tr>
<td>4</td>
<td>贫困</td>
<td>5000</td>
<td>0</td>
</tr>
</tbody></table>
<p>需求查询1:在上一个需求上面从工资等级表中查出工资等级</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>name<span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span> e<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> sl<span class="token punctuation">.</span>level_name
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
         <span class="token keyword">JOIN</span> salary_level sl <span class="token keyword">ON</span> e<span class="token punctuation">.</span>salary <span class="token operator">BETWEEN</span> sl<span class="token punctuation">.</span>MIN <span class="token operator">AND</span> sl<span class="token punctuation">.</span>MAX<span class="token punctuation">;</span> <span class="token comment"># between必须是底点在前，高点在后。</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>ass_name</th>
<th>salary</th>
<th>level_name</th>
</tr>
</thead>
<tbody><tr>
<td>王二</td>
<td>辽宁德云色</td>
<td>11000</td>
<td>中等收入</td>
</tr>
<tr>
<td>郭德纲</td>
<td>北京德云色</td>
<td>18000</td>
<td>中等收入</td>
</tr>
<tr>
<td>于谦</td>
<td>天津德云色</td>
<td>13000</td>
<td>中等收入</td>
</tr>
<tr>
<td>张三</td>
<td>北京德云色</td>
<td>9000</td>
<td>低收入</td>
</tr>
<tr>
<td>李四</td>
<td>北京德云色</td>
<td>6000</td>
<td>低收入</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>辽宁德云色</td>
<td>8000</td>
<td>低收入</td>
</tr>
<tr>
<td>王平</td>
<td>天津德云色</td>
<td>5000</td>
<td>低收入</td>
</tr>
<tr>
<td>二妞</td>
<td>辽宁德云色</td>
<td>3000</td>
<td>贫困</td>
</tr>
<tr>
<td>王平</td>
<td>天津德云色</td>
<td>5000</td>
<td>贫困</td>
</tr>
<tr>
<td>二傻</td>
<td>天津德云色</td>
<td>4000</td>
<td>贫困</td>
</tr>
<tr>
<td>少侯爷</td>
<td>天津德云色</td>
<td>3000</td>
<td>贫困</td>
</tr>
</tbody></table>
<p>内连接数据表之间不一定有同名的字段，只要逻辑可以相关的，也可以相连。</p>
<p>需求2:查处和郭德纲在一个社团的而且工作相同</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e2<span class="token punctuation">.</span>name<span class="token punctuation">,</span> e2<span class="token punctuation">.</span>join_date<span class="token punctuation">,</span> e2<span class="token punctuation">.</span><span class="token keyword">work</span><span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee e1
         <span class="token keyword">JOIN</span> employee e2 <span class="token keyword">ON</span> e1<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> e2<span class="token punctuation">.</span>ass_no <span class="token operator">AND</span> e1<span class="token punctuation">.</span><span class="token keyword">work</span> <span class="token operator">=</span> e2<span class="token punctuation">.</span><span class="token keyword">work</span>
         <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e1<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
<span class="token keyword">WHERE</span> e1<span class="token punctuation">.</span>name <span class="token operator">=</span> <span class="token string">'郭德纲'</span>
  <span class="token operator">AND</span> e2<span class="token punctuation">.</span>name <span class="token operator">!=</span> <span class="token string">'郭德纲'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>join_date</th>
<th>work</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>张三</td>
<td>1994-05-06</td>
<td>逗哏</td>
<td>北京德云色</td>
</tr>
</tbody></table>
<p>需求3:查询所有员工中超过平均工资(包括等于)的人。</p>
<p>首先看一下平均工资</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>AVG(salary)</th>
<th>COUNT(*)</th>
</tr>
</thead>
<tbody><tr>
<td>8000.0000</td>
<td>10</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e1<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>name <span class="token comment"># 选择展示字段</span>
<span class="token keyword">FROM</span> employee e1
         <span class="token keyword">JOIN</span> employee e2 <span class="token comment"># 建立查询范围</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> e1<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>name <span class="token comment"># 分组使用聚合</span>
<span class="token keyword">HAVING</span> e1<span class="token punctuation">.</span>salary <span class="token operator">>=</span> <span class="token function">AVG</span><span class="token punctuation">(</span>e2<span class="token punctuation">.</span>salary<span class="token punctuation">)</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>salary</th>
<th>name</th>
</tr>
</thead>
<tbody><tr>
<td>8000</td>
<td>岳云鹏</td>
</tr>
<tr>
<td>9000</td>
<td>张三</td>
</tr>
<tr>
<td>11000</td>
<td>王二</td>
</tr>
<tr>
<td>13000</td>
<td>于谦</td>
</tr>
<tr>
<td>18000</td>
<td>郭德纲</td>
</tr>
</tbody></table>
<p>其他方法，也可以先把平均工资查出来，然后当成一张表去join(这种写法不是子查询)</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e1<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> e1<span class="token punctuation">.</span>name
<span class="token keyword">FROM</span> employee e1
         <span class="token keyword">JOIN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span> AVG_SALARY <span class="token keyword">FROM</span> employee<span class="token punctuation">)</span> AVG <span class="token comment"># 平均工资的结果当成一张表来join</span>
              <span class="token keyword">ON</span> e1<span class="token punctuation">.</span>salary <span class="token operator">></span> AVG<span class="token punctuation">.</span>AVG_SALARY<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>需求4：查询社团的平均工资，最低，最高工资，以及最低和最高工资等级</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> GROUP_CONCAT<span class="token punctuation">(</span>e<span class="token punctuation">.</span>name<span class="token punctuation">)</span><span class="token punctuation">,</span>
       a<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span>
       <span class="token function">avg</span><span class="token punctuation">(</span>e<span class="token punctuation">.</span>salary<span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token string">'平均工资'</span><span class="token punctuation">,</span>
       <span class="token function">MAX</span><span class="token punctuation">(</span>e<span class="token punctuation">.</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">MIN</span><span class="token punctuation">(</span>e<span class="token punctuation">.</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span>
       GROUP_CONCAT<span class="token punctuation">(</span>sl<span class="token punctuation">.</span>level_name<span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token function">MIN</span><span class="token punctuation">(</span>sl<span class="token punctuation">.</span>id<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
         <span class="token keyword">JOIN</span> salary_level sl <span class="token keyword">ON</span> e<span class="token punctuation">.</span>salary <span class="token operator">between</span> sl<span class="token punctuation">.</span>MIN <span class="token operator">and</span> sl<span class="token punctuation">.</span>MAX
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">.</span>ass_name
<span class="token keyword">HAVING</span> <span class="token function">MAX</span><span class="token punctuation">(</span>e<span class="token punctuation">.</span>salary<span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>GROUP_CONCAT(e.name)</th>
<th>ass_name</th>
<th>平均工资</th>
<th>MAX(e.salary)</th>
<th>MIN(e.salary)</th>
<th>GROUP_CONCAT(sl.level_name)</th>
<th>MIN(sl.id)</th>
</tr>
</thead>
<tbody><tr>
<td>张三,李四,郭德纲</td>
<td>北京德云色</td>
<td>11000.0000</td>
<td>18000</td>
<td>6000</td>
<td>低收入,低收入,中等收入</td>
<td>2</td>
</tr>
<tr>
<td>王平,于谦,少侯爷,二傻,王平</td>
<td>天津德云色</td>
<td>6000.0000</td>
<td>13000</td>
<td>3000</td>
<td>贫困,中等收入,贫困,贫困,低收入</td>
<td>2</td>
</tr>
<tr>
<td>王二,岳云鹏,二妞</td>
<td>辽宁德云色</td>
<td>7333.3333</td>
<td>11000</td>
<td>3000</td>
<td>中等收入,低收入,贫困</td>
<td>2</td>
</tr>
</tbody></table>
<h4 id="6-3-3-表的外连接-LEFT-RIGHT-JOIN"><a href="#6-3-3-表的外连接-LEFT-RIGHT-JOIN" class="headerlink" title="6.3.3 表的外连接(LEFT/RIGHT JOIN)"></a>6.3.3 表的外连接(LEFT/RIGHT JOIN)</h4><blockquote>
<p>外连接，是指不管是否符合条件，记录都要保留在结果集合中。</p>
</blockquote>
<p>为什么要使用外连接</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201207080129.png" srcset="/img/loading.gif" alt="image-20201207080123593"></p>
<p>需求1:查询员工所属的社团</p>
<blockquote>
<p>我们先来试一下，比如往演员里面放一个临时演员，没有所属的社团，但是查询所有演员的社团的时候，又不能把这个演员的数据忽略，应该怎么写呢？</p>
</blockquote>
<p>首先看一下员工表数据</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
<td>10</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>3000</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>11</td>
<td>刘能</td>
<td>2000</td>
<td>二人转</td>
<td>2019-12-26</td>
<td>3</td>
<td>NULL</td>
</tr>
</tbody></table>
<p>11号员工刘能是临时演员没有社团，此时我们想关联社团表查询一些社团信息</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>name<span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>使用之前的查询方式</p>
<div class="code-wrapper"><pre class="line-numbers language-none"><code class="language-none">SELECT *
FROM employee;
SELECT e.name, a.ass_name
FROM employee e
         JOIN associations a on e.ass_no &#x3D; a.ass_no;<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>张三</td>
<td>北京德云色</td>
</tr>
<tr>
<td>李四</td>
<td>北京德云色</td>
</tr>
<tr>
<td>王二</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>郭德纲</td>
<td>北京德云色</td>
</tr>
<tr>
<td>于谦</td>
<td>天津德云色</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>二妞</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>王平</td>
<td>天津德云色</td>
</tr>
<tr>
<td>二傻</td>
<td>天津德云色</td>
</tr>
<tr>
<td>少侯爷</td>
<td>天津德云色</td>
</tr>
</tbody></table>
<p>发现少了刘能，我们使用左外连接（保留join左边的表的完整信息，右边对应不上的用null表示）</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>name<span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee e
       <span class="token keyword">LEFT</span>  <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<p>查询结果：</p>
<table>
<thead>
<tr>
<th>name</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>张三</td>
<td>北京德云色</td>
</tr>
<tr>
<td>李四</td>
<td>北京德云色</td>
</tr>
<tr>
<td>郭德纲</td>
<td>北京德云色</td>
</tr>
<tr>
<td>王二</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>二妞</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>于谦</td>
<td>天津德云色</td>
</tr>
<tr>
<td>王平</td>
<td>天津德云色</td>
</tr>
<tr>
<td>二傻</td>
<td>天津德云色</td>
</tr>
<tr>
<td>少侯爷</td>
<td>天津德云色</td>
</tr>
<tr>
<td>刘能</td>
<td>NULL</td>
</tr>
</tbody></table>
<p>需求2:查询社团里面员工的人数,但是没有社团的人也要显示出来。</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> a<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">,</span> GROUP_CONCAT<span class="token punctuation">(</span>e<span class="token punctuation">.</span>name<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> associations a <span class="token keyword">on</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">.</span>ass_name<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>ass_name</th>
<th>COUNT(*)</th>
<th>GROUP_CONCAT(e.name)</th>
</tr>
</thead>
<tbody><tr>
<td>NULL</td>
<td>1</td>
<td>刘能</td>
</tr>
<tr>
<td>北京德云色</td>
<td>3</td>
<td>张三,李四,郭德纲</td>
</tr>
<tr>
<td>天津德云色</td>
<td>4</td>
<td>二傻,于谦,少侯爷,王平</td>
</tr>
<tr>
<td>辽宁德云色</td>
<td>3</td>
<td>二妞,王二,岳云鹏</td>
</tr>
</tbody></table>
<h3 id="6-4-子查询"><a href="#6-4-子查询" class="headerlink" title="6.4 子查询"></a>6.4 子查询</h3><blockquote>
<p>子查询是一种查询中嵌套查询的语句</p>
</blockquote>
<p>where子句的子查询不推荐使用，因为每一次都要执行嵌套的查询语句 不推荐使用</p>
<p>推荐的子查询方式，将子查询的数据当作一张表，使用from语句的子查询是可以使用的</p>
<h2 id="7-数据的增删改"><a href="#7-数据的增删改" class="headerlink" title="7.数据的增删改"></a>7.数据的增删改</h2><h3 id="7-1数据添加INSERT"><a href="#7-1数据添加INSERT" class="headerlink" title="7.1数据添加INSERT"></a>7.1数据添加<code>INSERT</code></h3><blockquote>
<p><code>INSERT</code>语法,<code>INSERT</code> 方言语法,<code>INSERT</code>子查询,<code>IGNORE</code> 关键字</p>
<p><code>INSERT</code>语句可以写入一条或多条记录</p>
</blockquote>
<h4 id="7-1-1-INSERT-基本使用"><a href="#7-1-1-INSERT-基本使用" class="headerlink" title="7.1.1 INSERT 基本使用"></a>7.1.1 <code>INSERT</code> 基本使用</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> salary_level<span class="token punctuation">(</span>LEVEL_NAME<span class="token punctuation">,</span> MAX<span class="token punctuation">,</span> MIN<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'高收入'</span><span class="token punctuation">,</span> <span class="token number">100000</span><span class="token punctuation">,</span> <span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'中等收入'</span><span class="token punctuation">,</span> <span class="token number">20000</span><span class="token punctuation">,</span> <span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'低收入'</span><span class="token punctuation">,</span> <span class="token number">10000</span><span class="token punctuation">,</span> <span class="token number">5000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'贫困'</span><span class="token punctuation">,</span> <span class="token number">5000</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> salary_level<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>level_name</th>
<th>MAX</th>
<th>MIN</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>高收入</td>
<td>100000</td>
<td>20000</td>
</tr>
<tr>
<td>2</td>
<td>中等收入</td>
<td>20000</td>
<td>10000</td>
</tr>
<tr>
<td>3</td>
<td>低收入</td>
<td>10000</td>
<td>5000</td>
</tr>
<tr>
<td>4</td>
<td>贫困</td>
<td>5000</td>
<td>0</td>
</tr>
</tbody></table>
<h4 id="7-1-2-INSERT中使用子查询"><a href="#7-1-2-INSERT中使用子查询" class="headerlink" title="7.1.2 INSERT中使用子查询"></a>7.1.2 INSERT中使用子查询</h4><p>需求1:插入一个员工，社团所属为<code>北京德云色</code></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>name<span class="token punctuation">,</span> salary<span class="token punctuation">,</span> <span class="token keyword">work</span><span class="token punctuation">,</span> join_date<span class="token punctuation">,</span> <span class="token keyword">level</span><span class="token punctuation">,</span> ass_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'常昊'</span><span class="token punctuation">,</span> <span class="token number">100000</span><span class="token punctuation">,</span> <span class="token string">'现场导演'</span><span class="token punctuation">,</span> <span class="token string">'2020-10-20'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> ass_no
    <span class="token keyword">FROM</span> associations
    <span class="token keyword">WHERE</span> ass_name <span class="token operator">=</span> <span class="token string">'北京德云色'</span>
<span class="token punctuation">)</span><span class="token punctuation">)</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>​    </p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
<td>10</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>3000</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>11</td>
<td>刘能</td>
<td>2000</td>
<td>二人转</td>
<td>2019-12-26</td>
<td>3</td>
<td>NULL</td>
</tr>
<tr>
<td>12</td>
<td>常昊</td>
<td>100000</td>
<td>现场导演</td>
<td>2020-10-20</td>
<td>1</td>
<td>10</td>
</tr>
</tbody></table>
<h4 id="7-1-3-INSERT-语句方言-mysql专属"><a href="#7-1-3-INSERT-语句方言-mysql专属" class="headerlink" title="7.1.3 INSERT 语句方言(mysql专属)"></a>7.1.3 INSERT 语句方言(mysql专属)</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> 表名 <span class="token keyword">SET</span> 字段<span class="token number">1</span><span class="token operator">=</span>值<span class="token number">1</span>，字段<span class="token number">2</span><span class="token operator">=</span>值<span class="token number">2</span>，<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> associations
<span class="token keyword">SET</span> ass_name<span class="token operator">=</span><span class="token string">'闹大利亚德云色'</span><span class="token punctuation">,</span>
    ass_no<span class="token operator">=</span><span class="token number">99</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> associations<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>​    </p>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>99</td>
</tr>
</tbody></table>
<h4 id="7-1-4-IGNORE关键字"><a href="#7-1-4-IGNORE关键字" class="headerlink" title="7.1.4 IGNORE关键字"></a>7.1.4 IGNORE关键字</h4><blockquote>
<p>插入多条数据的时候，如果其中某一条记录和表里面的数据有冲突，忽略这条错误数据的插入，继续插入其他数据。</p>
</blockquote>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INTO</span> associations <span class="token punctuation">(</span>ass_name<span class="token punctuation">,</span> ass_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'闹大利亚德云色'</span><span class="token punctuation">,</span> <span class="token number">99</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment"># 这一条是重复的</span>
       <span class="token punctuation">(</span><span class="token string">'刚过利亚恺威而'</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> associations<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>99</td>
</tr>
<tr>
<td>6</td>
<td>刚过利亚恺威而</td>
<td>100</td>
</tr>
</tbody></table>
<h3 id="7-2-数据修改UPDATE"><a href="#7-2-数据修改UPDATE" class="headerlink" title="7.2 数据修改UPDATE"></a>7.2 数据修改<code>UPDATE</code></h3><blockquote>
<p><code>UPDATE</code>语法,<code>UPDATE</code>表连接</p>
</blockquote>
<p>​    </p>
<h4 id="7-2-1-UPDATE基本使用"><a href="#7-2-1-UPDATE基本使用" class="headerlink" title="7.2.1 UPDATE基本使用"></a>7.2.1 UPDATE基本使用</h4><div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> <span class="token punctuation">[</span><span class="token keyword">IGNORE</span><span class="token punctuation">]</span> 表名 <span class="token keyword">SET</span> 字段<span class="token number">1</span><span class="token operator">=</span>值<span class="token number">1</span><span class="token punctuation">,</span>字段<span class="token number">2</span><span class="token operator">=</span>值<span class="token number">2</span>，<span class="token punctuation">[</span><span class="token keyword">where</span> 条件<span class="token number">1</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">LIMIT</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>需求1:    把闹大利亚德云色的ass_no修改为40</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> associations
<span class="token keyword">SET</span> ass_no <span class="token operator">=</span> <span class="token number">40</span>
<span class="token keyword">WHERE</span> id <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> associations<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td></td>
</tr>
<tr>
<td></td>
<td>刚过利亚恺威而</td>
<td>100</td>
</tr>
</tbody></table>
<p>需求2:把员工工资排名前三的降薪百分之10</p>
<p>先看看现在工资排名情况</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>12</td>
<td>常昊</td>
<td>100000</td>
<td>现场导演</td>
<td>2020-10-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>18000</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13000</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
<td>10</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>2900</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>2900</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>11</td>
<td>刘能</td>
<td>1900</td>
<td>二人转</td>
<td>2019-12-26</td>
<td>3</td>
<td>NULL</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> employee
<span class="token keyword">SET</span> salary <span class="token operator">=</span> salary<span class="token operator">*</span><span class="token number">0.9</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token number">3</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span><span class="token punctuation">;</span>	<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>12</td>
<td>常昊</td>
<td>90000</td>
<td>现场导演</td>
<td>2020-10-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>4</td>
<td>郭德纲</td>
<td>16200</td>
<td>逗哏</td>
<td>2000-05-12</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>11700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>11000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>1</td>
<td>张三</td>
<td>9000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>8000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>2</td>
<td>李四</td>
<td>6000</td>
<td>捧哏</td>
<td>1996-05-16</td>
<td>2</td>
<td>10</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>5000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>2900</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>2900</td>
<td>报幕</td>
<td>1993-12-09</td>
<td>3</td>
<td>30</td>
</tr>
<tr>
<td>11</td>
<td>刘能</td>
<td>1900</td>
<td>二人转</td>
<td>2019-12-26</td>
<td>3</td>
<td>NULL</td>
</tr>
</tbody></table>
<p>需求3:将少侯爷<code>从闹大利亚德云色调去北京德云色,工作变为逗哏</code></p>
<p>先看看此时的员工分布情况</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> a<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span> a<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span>GROUP_CONCAT<span class="token punctuation">(</span>e<span class="token punctuation">.</span>name<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">JOIN</span> associations a
<span class="token keyword">where</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> a<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span>a<span class="token punctuation">.</span>ass_name<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>ass_no</th>
<th>ass_name</th>
<th>GROUP_CONCAT(e.name)</th>
</tr>
</thead>
<tbody><tr>
<td>10</td>
<td>北京德云色</td>
<td>岳云鹏,刘能,郭德纲,二傻,李四,二妞</td>
</tr>
<tr>
<td>20</td>
<td>辽宁德云色</td>
<td>张三,常昊,王二,王平</td>
</tr>
<tr>
<td>30</td>
<td>天津德云色</td>
<td>于谦</td>
</tr>
<tr>
<td>40</td>
<td>闹大利亚德云色</td>
<td>少侯爷</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> employee
<span class="token keyword">SET</span> ass_no <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> ass_no <span class="token keyword">FROM</span> associations <span class="token keyword">WHERE</span> ass_name <span class="token operator">=</span> <span class="token string">'北京德云色'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token keyword">work</span> <span class="token operator">=</span> <span class="token string">'逗哏'</span>
<span class="token keyword">WHERE</span> name <span class="token operator">=</span> <span class="token string">'少侯爷'</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201208112042.png" srcset="/img/loading.gif" alt="image-20201208112036585"></p>
<p>数据已经更新成功，但是这种更新中带着的查询叫相关子查询，不适合用于批量数据修改,下面我们学习<code>update中的表连接</code>完成需求3</p>
<h4 id="7-2-2-UPDATE的表连接"><a href="#7-2-2-UPDATE的表连接" class="headerlink" title="7.2.2 UPDATE的表连接"></a>7.2.2 UPDATE的表连接</h4><blockquote>
<p>因为相关子查询效率非常低,所以我们可以使用表连接的方式来改造update语句</p>
<p>表连接的UPDATE语句可以修改多张表的记录</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201208113207.png" srcset="/img/loading.gif" alt="image-20201208113207363"></p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201208113329.png" srcset="/img/loading.gif" alt="image-20201208113329223"></p>
<p>我们再根据上面改完的结果<code>把少侯爷放回 闹大利亚德云色，工作变为幕后</code></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> employee e <span class="token keyword">JOIN</span> associations a
<span class="token keyword">SET</span> e<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span>e<span class="token punctuation">.</span><span class="token keyword">work</span> <span class="token operator">=</span> <span class="token string">'幕后'</span>
<span class="token keyword">WHERE</span> e<span class="token punctuation">.</span>name <span class="token operator">=</span> <span class="token string">'少侯爷'</span>
  <span class="token operator">AND</span> a<span class="token punctuation">.</span>ass_name <span class="token operator">=</span> <span class="token string">'闹大利亚德云色'</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201208114236.png" srcset="/img/loading.gif" alt="image-20201208114236350"></p>
<p>需求4:员工薪资等级id大于3的（贫困员工）增加1000元工资</p>
<p>先把贫困的筛选出来看看</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> e<span class="token punctuation">.</span>name<span class="token punctuation">,</span> e<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> sl<span class="token punctuation">.</span>level_name<span class="token punctuation">,</span>sl<span class="token punctuation">.</span>id <span class="token keyword">AS</span> <span class="token string">'工资等级id'</span>
<span class="token keyword">FROM</span> employee e
         <span class="token keyword">JOIN</span> salary_level sl
<span class="token keyword">WHERE</span> e<span class="token punctuation">.</span>salary <span class="token operator">BETWEEN</span> sl<span class="token punctuation">.</span>MIN <span class="token operator">AND</span> sl<span class="token punctuation">.</span>MAX
  <span class="token operator">AND</span> sl<span class="token punctuation">.</span>id <span class="token operator">></span> <span class="token number">3</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> e<span class="token punctuation">.</span>salary<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>level_name</th>
<th>工资等级id</th>
</tr>
</thead>
<tbody><tr>
<td>王平</td>
<td>1000</td>
<td>贫困</td>
<td>4</td>
</tr>
<tr>
<td>二傻</td>
<td>3000</td>
<td>贫困</td>
<td>4</td>
</tr>
<tr>
<td>刘能</td>
<td>3000</td>
<td>贫困</td>
<td>4</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">UPDATE</span> employee E <span class="token keyword">JOIN</span> salary_level SL
<span class="token keyword">SET</span> E<span class="token punctuation">.</span>salary <span class="token operator">=</span> E<span class="token punctuation">.</span>salary <span class="token operator">+</span> <span class="token number">1000</span>
<span class="token keyword">WHERE</span> SL<span class="token punctuation">.</span>id <span class="token operator">></span> <span class="token number">3</span> <span class="token comment"># 只把雇员的工资和贫困的薪资范围拿出来对比</span>
<span class="token operator">AND</span> E<span class="token punctuation">.</span>salary <span class="token operator">BETWEEN</span> SL<span class="token punctuation">.</span>MIN <span class="token operator">AND</span> SL<span class="token punctuation">.</span>MAX<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>level_name</th>
<th>工资等级id</th>
</tr>
</thead>
<tbody><tr>
<td>王平</td>
<td>2000</td>
<td>贫困</td>
<td>4</td>
</tr>
<tr>
<td>二傻</td>
<td>4000</td>
<td>贫困</td>
<td>4</td>
</tr>
<tr>
<td>刘能</td>
<td>4000</td>
<td>贫困</td>
<td>4</td>
</tr>
</tbody></table>
<h3 id="7-3-数据删除DELETE"><a href="#7-3-数据删除DELETE" class="headerlink" title="7.3 数据删除DELETE"></a>7.3 数据删除<code>DELETE</code></h3><h4 id="7-3-1-DELETE-基础使用"><a href="#7-3-1-DELETE-基础使用" class="headerlink" title="7.3.1 DELETE 基础使用"></a>7.3.1 DELETE 基础使用</h4><blockquote>
<p><code>DELETE</code>语法，<code>DELETE</code>表连接</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201208124006.png" srcset="/img/loading.gif" alt="image-20201208124006220"></p>
<p>需求1:删除所有员工中工资最高的</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> name<span class="token punctuation">,</span> salary
<span class="token keyword">FROM</span> employee
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
</tr>
</thead>
<tbody><tr>
<td>常昊</td>
<td>92000</td>
</tr>
<tr>
<td>郭德纲</td>
<td>18200</td>
</tr>
<tr>
<td>王二</td>
<td>14000</td>
</tr>
<tr>
<td>于谦</td>
<td>13700</td>
</tr>
<tr>
<td>张三</td>
<td>12000</td>
</tr>
<tr>
<td>李四</td>
<td>11000</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>11000</td>
</tr>
<tr>
<td>二妞</td>
<td>8900</td>
</tr>
<tr>
<td>少侯爷</td>
<td>8900</td>
</tr>
<tr>
<td>二傻</td>
<td>4000</td>
</tr>
<tr>
<td>刘能</td>
<td>4000</td>
</tr>
<tr>
<td>王平</td>
<td>2000</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
</tr>
</thead>
<tbody><tr>
<td>郭德纲</td>
<td>18200</td>
</tr>
<tr>
<td>王二</td>
<td>14000</td>
</tr>
<tr>
<td>于谦</td>
<td>13700</td>
</tr>
<tr>
<td>张三</td>
<td>12000</td>
</tr>
<tr>
<td>李四</td>
<td>11000</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>11000</td>
</tr>
<tr>
<td>二妞</td>
<td>8900</td>
</tr>
<tr>
<td>少侯爷</td>
<td>8900</td>
</tr>
<tr>
<td>二傻</td>
<td>4000</td>
</tr>
<tr>
<td>刘能</td>
<td>4000</td>
</tr>
<tr>
<td>王平</td>
<td>2000</td>
</tr>
</tbody></table>
<p>需求2:删除北京德云色所有员工中工资最高的那个</p>
<p>先看一下，北京德云色员工情况</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>name<span class="token punctuation">,</span> E<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> E<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no
  <span class="token operator">AND</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">10</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>ass_no</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>李四</td>
<td>11000</td>
<td>10</td>
<td>北京德云色</td>
</tr>
<tr>
<td>二傻</td>
<td>4000</td>
<td>10</td>
<td>北京德云色</td>
</tr>
<tr>
<td>刘能</td>
<td>4000</td>
<td>10</td>
<td>北京德云色</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span>
<span class="token keyword">FROM</span> employee
<span class="token keyword">WHERE</span> ass_no <span class="token operator">=</span> <span class="token number">10</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> salary <span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>ass_no</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>二傻</td>
<td>4000</td>
<td>10</td>
<td>北京德云色</td>
</tr>
<tr>
<td>刘能</td>
<td>4000</td>
<td>10</td>
<td>北京德云色</td>
</tr>
</tbody></table>
<h4 id="7-3-2-DELETE语句的表连接"><a href="#7-3-2-DELETE语句的表连接" class="headerlink" title="7.3.2 DELETE语句的表连接"></a>7.3.2 DELETE语句的表连接</h4><h4 id="-1"><a href="#-1" class="headerlink" title=""></a><img src="http://qiniuyun.quancundexiwang.wang/20201208155809.png" srcset="/img/loading.gif" alt="image-20201208155809857"></h4><p>需求1:请来的老艺术家当表演嘉宾，结束后，将表演嘉宾这个临时社团，和里面的演员都删掉</p>
<p>删除前：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token comment"># 插入新的临时ass</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> associations<span class="token punctuation">(</span>ass_no<span class="token punctuation">,</span> ass_name<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">,</span> <span class="token string">'表演嘉宾'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment"># 插入新的临时员工</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>name<span class="token punctuation">,</span> <span class="token keyword">work</span><span class="token punctuation">,</span> ass_no<span class="token punctuation">)</span>
<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'马三立'</span><span class="token punctuation">,</span> <span class="token string">'老艺术家'</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'刘宝瑞'</span><span class="token punctuation">,</span> <span class="token string">'老艺术家'</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'张文顺'</span><span class="token punctuation">,</span> <span class="token string">'老艺术家'</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'侯耀文'</span><span class="token punctuation">,</span> <span class="token string">'老艺术家'</span><span class="token punctuation">,</span> <span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
       
       
<span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>name<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_no
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> associations<span class="token punctuation">;</span> <span class="token comment"># 查看</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
<tr>
<td>9</td>
<td>表演嘉宾</td>
<td>50</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th>name</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>张三</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>王二</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>于谦</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>岳云鹏</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
<tr>
<td>二妞</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
<tr>
<td>王平</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>二傻</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>少侯爷</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
<tr>
<td>刘能</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>马三立</td>
<td>表演嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>刘宝瑞</td>
<td>表演嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>张文顺</td>
<td>表演嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>侯耀文</td>
<td>表演嘉宾</td>
<td>50</td>
</tr>
</tbody></table>
<p>删除操作：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span> E<span class="token punctuation">,</span>A <span class="token comment"># 删除表中记录</span>
<span class="token keyword">FROM</span> associations A
         <span class="token keyword">JOIN</span> employee E
<span class="token keyword">WHERE</span> A<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> E<span class="token punctuation">.</span>ass_no <span class="token comment">#删除员工ass_no = 50的</span>
  <span class="token operator">AND</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">;</span> <span class="token comment">#删除社团no=50</span>
  
<span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>name<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_name<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_no
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> associations<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>7</td>
<td>二妞</td>
<td>8900</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>8</td>
<td>王平</td>
<td>2000</td>
<td>报幕</td>
<td>2006-12-08</td>
<td>3</td>
<td>20</td>
</tr>
<tr>
<td>9</td>
<td>二傻</td>
<td>4000</td>
<td>报幕</td>
<td>1999-12-31</td>
<td>3</td>
<td>10</td>
</tr>
<tr>
<td>10</td>
<td>少侯爷</td>
<td>8900</td>
<td>幕后</td>
<td>1993-12-09</td>
<td>3</td>
<td>40</td>
</tr>
<tr>
<td>11</td>
<td>刘能</td>
<td>4000</td>
<td>二人转</td>
<td>2019-12-26</td>
<td>3</td>
<td>10</td>
</tr>
</tbody></table>
<p>需求2:删除每一个部门里面，工资低于平均工资的人;</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>22</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>24</td>
<td>李文山</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>40</td>
</tr>
<tr>
<td>27</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>28</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
</tbody></table>
<p>先看一下每个社团的平均工资,</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>E<span class="token punctuation">.</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'avg_value'</span>
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> E<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>ass_no</th>
<th>avg_value</th>
</tr>
</thead>
<tbody><tr>
<td>10</td>
<td>2000.0000</td>
</tr>
<tr>
<td>20</td>
<td>9000.0000</td>
</tr>
<tr>
<td>30</td>
<td>7350.0000</td>
</tr>
<tr>
<td>40</td>
<td>6000.0000</td>
</tr>
</tbody></table>
<p>我们可以查询的社团平均工资当成一个表，查出社团中平均工资最低的人</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>name<span class="token punctuation">,</span> E<span class="token punctuation">.</span>salary<span class="token punctuation">,</span> ASVG<span class="token punctuation">.</span>avg_value<span class="token punctuation">,</span> ASVG<span class="token punctuation">.</span>ass_no
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>E<span class="token punctuation">.</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'avg_value'</span>
    <span class="token keyword">FROM</span> employee E
             <span class="token keyword">JOIN</span> associations A
    <span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no
    <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> E<span class="token punctuation">.</span>ass_no
<span class="token punctuation">)</span> ASVG
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>salary <span class="token operator">&lt;</span> ASVG<span class="token punctuation">.</span>avg_value
  <span class="token operator">AND</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> ASVG<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>name</th>
<th>salary</th>
<th>avg_value</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>刘宝瑞</td>
<td>1000</td>
<td>2000.0000</td>
<td>10</td>
</tr>
<tr>
<td>李文山</td>
<td>1000</td>
<td>6000.0000</td>
<td>40</td>
</tr>
<tr>
<td>张文顺</td>
<td>1000</td>
<td>7350.0000</td>
<td>30</td>
</tr>
<tr>
<td>侯耀文</td>
<td>1000</td>
<td>9000.0000</td>
<td>20</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span> E
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> <span class="token punctuation">(</span>
    <span class="token keyword">SELECT</span> E<span class="token punctuation">.</span>ass_no<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>E<span class="token punctuation">.</span>salary<span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'avg_value'</span>
    <span class="token keyword">FROM</span> employee E
             <span class="token keyword">JOIN</span> associations A
    <span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no
    <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> E<span class="token punctuation">.</span>ass_no
<span class="token punctuation">)</span> ASVG
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>salary <span class="token operator">&lt;</span> ASVG<span class="token punctuation">.</span>avg_value
  <span class="token operator">AND</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> ASVG<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>​    <code>2 ms 中有 4 行受到影响</code></p>
<p>结果：把每个社团中工资最少的删除</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
</tbody></table>
<p>需求3:删除临场嘉宾团体的员工（ass_no:50）以及没有社团的员工（ass_no:null）;</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>30</td>
<td>马三立</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>31</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>32</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>33</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>38</td>
<td>单田芳</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
</tr>
<tr>
<td>39</td>
<td>呼呼哈儿</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
</tr>
<tr>
<td>40</td>
<td>哈尔</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
</tr>
<tr>
<td>41</td>
<td>罗密欧</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
<tr>
<td>42</td>
<td>朱丽叶</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
<tr>
<td>43</td>
<td>密西西比</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
</tbody></table>
<p>首先把这些人筛选出来看看</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span>
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> associations A <span class="token keyword">on</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
<span class="token keyword">WHERE</span> A<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">50</span> <span class="token operator">OR</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>41</td>
<td>罗密欧</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
<td>10</td>
<td>临时嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>42</td>
<td>朱丽叶</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
<td>10</td>
<td>临时嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>43</td>
<td>密西西比</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
<td>10</td>
<td>临时嘉宾</td>
<td>50</td>
</tr>
<tr>
<td>38</td>
<td>单田芳</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
<tr>
<td>39</td>
<td>呼呼哈儿</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
<tr>
<td>40</td>
<td>哈尔</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
</tbody></table>
<p>执行删除</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">DELETE</span> E
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> associations A <span class="token keyword">on</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> a<span class="token punctuation">.</span>ass_no
<span class="token keyword">WHERE</span> A<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">50</span> <span class="token operator">OR</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">IS</span> <span class="token boolean">NULL</span> <span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p> 结果：</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>30</td>
<td>马三立</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>31</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>32</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>33</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>20</td>
</tr>
</tbody></table>
<h4 id="7-3-3-DELETE-快速删除整张表的记录"><a href="#7-3-3-DELETE-快速删除整张表的记录" class="headerlink" title="7.3.3 DELETE 快速删除整张表的记录"></a>7.3.3 DELETE 快速删除整张表的记录</h4><blockquote>
<p>DELETE是在事务机制下执行的删除操作，删除记录之前，先把将要删除的记录保存在日志里，然后在删除记录。</p>
</blockquote>
<p>语法</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">TRUNCATE</span> <span class="token keyword">TABLE</span> 表名<span class="token punctuation">;</span> <span class="token comment">#删除数据表（绕过事务机制，一次只能删除一张表）</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<h2 id="8-MYSQL常用函数"><a href="#8-MYSQL常用函数" class="headerlink" title="8.MYSQL常用函数"></a>8.MYSQL常用函数</h2><blockquote>
<p>常用函数主要有下面四类</p>
</blockquote>
<ul>
<li>数字函数</li>
<li>字符函数</li>
<li>日期函数</li>
<li>条件函数</li>
</ul>
<h3 id="8-1-数字函数"><a href="#8-1-数字函数" class="headerlink" title="8.1 数字函数"></a>8.1 数字函数</h3><p><img src="http://qiniuyun.quancundexiwang.wang/20201209074942.png" srcset="/img/loading.gif" alt="image-20201209074937437"></p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209075336.png" srcset="/img/loading.gif" alt="image-20201209075336185"></p>
<h3 id="8-2-日期函数"><a href="#8-2-日期函数" class="headerlink" title="8.2 日期函数"></a>8.2 日期函数</h3><h4 id="8-2-1-获取系统时间的函数"><a href="#8-2-1-获取系统时间的函数" class="headerlink" title="8.2.1 获取系统时间的函数"></a>8.2.1 获取系统时间的函数</h4><p><img src="http://qiniuyun.quancundexiwang.wang/20201209075644.png" srcset="/img/loading.gif" alt="image-20201209075644858"></p>
<h4 id="8-2-2-日期格式化函数"><a href="#8-2-2-日期格式化函数" class="headerlink" title="8.2.2 日期格式化函数"></a>8.2.2 日期格式化函数</h4><p><img src="http://qiniuyun.quancundexiwang.wang/20201209075817.png" srcset="/img/loading.gif" alt="image-20201209075817448"></p>
<p>常用占位符</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209075931.png" srcset="/img/loading.gif" alt="image-20201209075931072"></p>
<h4 id="8-2-3-日期的计算"><a href="#8-2-3-日期的计算" class="headerlink" title="8.2.3 日期的计算"></a>8.2.3 日期的计算</h4><p><span style='color:red'>注意日期和日期之间不能相加减，日期也不能和数字相加减，有专门的日期函数处理</span></p>
<ul>
<li><p>日期偏移计算<code> DATE_ADD(日期，INTERVAL 偏移量 时间单位)</code>,可以实现日期偏移计算，而且时间单位很灵活。</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209080523.png" srcset="/img/loading.gif" alt="image-20201209080522972"></p>
</li>
</ul>
<ul>
<li>日期相差天数 <code>DATEDIFF(日期，日期)</code></li>
</ul>
<h3 id="8-3-字符函数"><a href="#8-3-字符函数" class="headerlink" title="8.3 字符函数"></a>8.3 字符函数</h3><p><img src="http://qiniuyun.quancundexiwang.wang/20201209080911.png" srcset="/img/loading.gif" alt="image-20201209080911895"></p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209081240.png" srcset="/img/loading.gif" alt="image-20201209081239932"></p>
<h3 id="8-4-条件函数"><a href="#8-4-条件函数" class="headerlink" title="8.4 条件函数"></a>8.4 条件函数</h3><h4 id="8-4-1-IF相关语句"><a href="#8-4-1-IF相关语句" class="headerlink" title="8.4.1 IF相关语句"></a>8.4.1 <code>IF</code>相关语句</h4><ul>
<li><code>IFNULL(值，默认值)</code> ,当值为null，则使用 默认值，否则就用值</li>
<li><code>IF(表达式,值1，值2)</code> 很像三元运算符，表达式成立用值1，不成立用值2. </li>
</ul>
<p>需求：北京德云色的员工发烤鸭，其他的员工发月饼，输出每个人的礼物</p>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>30</td>
<td>马三立</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>31</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>32</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>33</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>20</td>
</tr>
</tbody></table>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">IF</span><span class="token punctuation">(</span>E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">,</span> <span class="token string">'烤鸭'</span><span class="token punctuation">,</span> <span class="token string">'月饼'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'节日礼物'</span><span class="token punctuation">,</span> E<span class="token punctuation">.</span>name<span class="token punctuation">,</span> A<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>节日礼物</th>
<th>name</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>月饼</td>
<td>张三</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>王二</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>于谦</td>
<td>天津德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>岳云鹏</td>
<td>闹大利亚德云色</td>
</tr>
<tr>
<td>烤鸭</td>
<td>徐德亮</td>
<td>北京德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>马三立</td>
<td>天津德云色</td>
</tr>
<tr>
<td>烤鸭</td>
<td>刘宝瑞</td>
<td>北京德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>张文顺</td>
<td>天津德云色</td>
</tr>
<tr>
<td>月饼</td>
<td>侯耀文</td>
<td>辽宁德云色</td>
</tr>
</tbody></table>
<h4 id="8-4-2-条件语句，case-when"><a href="#8-4-2-条件语句，case-when" class="headerlink" title="8.4.2  条件语句，case when"></a>8.4.2  条件语句，<code>case when</code></h4><p><img src="http://qiniuyun.quancundexiwang.wang/20201209082801.png" srcset="/img/loading.gif" alt="image-20201209082801506"></p>
<p>需求：依然发礼物，但是具体到每个部门都有不同的礼物，北京德云色的员工发烤鸭，天津德云色发煎饼果子，辽宁德云色发猪肉粉条，闹大利亚德云色发黄豆和冰水。</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">CASE</span>
           <span class="token keyword">WHEN</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">10</span> <span class="token keyword">THEN</span> <span class="token string">'烤鸭'</span>
           <span class="token keyword">WHEN</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">20</span> <span class="token keyword">THEN</span> <span class="token string">'猪肉粉条'</span>
           <span class="token keyword">WHEN</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">30</span> <span class="token keyword">THEN</span> <span class="token string">'煎饼果子'</span>
           <span class="token keyword">WHEN</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">40</span> <span class="token keyword">THEN</span> <span class="token string">'凉水黄豆'</span>
           <span class="token keyword">ELSE</span> <span class="token string">'啥都没有'</span> <span class="token keyword">END</span> <span class="token keyword">AS</span> <span class="token string">'礼物'</span><span class="token punctuation">,</span>
       E<span class="token punctuation">.</span>name<span class="token punctuation">,</span>
       A<span class="token punctuation">.</span>ass_name
<span class="token keyword">FROM</span> employee E
         <span class="token keyword">JOIN</span> associations A
<span class="token keyword">WHERE</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> A<span class="token punctuation">.</span>ass_no<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<table>
<thead>
<tr>
<th>礼物</th>
<th>name</th>
<th>ass_name</th>
</tr>
</thead>
<tbody><tr>
<td>猪肉粉条</td>
<td>张三</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>猪肉粉条</td>
<td>王二</td>
<td>辽宁德云色</td>
</tr>
<tr>
<td>煎饼果子</td>
<td>于谦</td>
<td>天津德云色</td>
</tr>
<tr>
<td>凉水黄豆</td>
<td>岳云鹏</td>
<td>闹大利亚德云色</td>
</tr>
<tr>
<td>烤鸭</td>
<td>徐德亮</td>
<td>北京德云色</td>
</tr>
<tr>
<td>煎饼果子</td>
<td>马三立</td>
<td>天津德云色</td>
</tr>
<tr>
<td>烤鸭</td>
<td>刘宝瑞</td>
<td>北京德云色</td>
</tr>
<tr>
<td>煎饼果子</td>
<td>张文顺</td>
<td>天津德云色</td>
</tr>
<tr>
<td>猪肉粉条</td>
<td>侯耀文</td>
<td>辽宁德云色</td>
</tr>
</tbody></table>
<h2 id="9-MYSQL的综合应用"><a href="#9-MYSQL的综合应用" class="headerlink" title="9.MYSQL的综合应用"></a>9.MYSQL的综合应用</h2><ul>
<li>掌握mysql的事务机制</li>
<li>了解数据连接池机制</li>
<li>mysql connector模块</li>
</ul>
<h3 id="9-1-MYSQL事务机制Transaction"><a href="#9-1-MYSQL事务机制Transaction" class="headerlink" title="9.1 MYSQL事务机制Transaction"></a>9.1 MYSQL事务机制<code>Transaction</code></h3><p>​    </p>
<h4 id="9-1-1事务的概念"><a href="#9-1-1事务的概念" class="headerlink" title="9.1.1事务的概念"></a>9.1.1事务的概念</h4><blockquote>
<p>事务是一个或多个sql语句组合成的整体，要么全部执行成功，要么全部执行失败。</p>
</blockquote>
<blockquote>
<p>假设一个网购付款的操作，用户付款后要涉及到订单状态更新、扣库存以及其他一系列动作，这就是一个事务，如果一切正常那就相安无事，一旦中间有某个环节异常，那整个事务就要回滚，总不能更新了订单状态但是不扣库存吧，这问题就大了。</p>
</blockquote>
<blockquote>
<p>避免sql语句直接操作数据文件,事务机制很像我们处理数据的时候，首先备份一份数据，然后再去做数据操作的.</p>
<p>MYSQL中是使用日志来完成事务机制，MYSQL共有五种日志，其中的<code>redo日志</code>，和<code>undo日志</code>与事务有关系。</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209103601.png" srcset="/img/loading.gif" alt="image-20201209103601007"></p>
<p><span style='color:red'>根据SQL语句对数据文件操作的记录，就会把这些数据拷贝到<code>undo日志</code>里面，然后执行修改的时候，将增删改查的操作记录在<code>redo日志</code>里面，然后没什么问题的话，会从<code>redo</code>同步数据到数据文件。</span></p>
<p><span style='color:blue'>对于每一条SQL语句,在其执行的时候，mysql会自动为其开启事务，但是如果我们需要多条语句保证执行成功，就需要手动开启事务机制.</span>    </p>
<p>语法：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">sql</span>语句<span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">sql</span>语句<span class="token number">2</span><span class="token punctuation">;</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">[</span><span class="token keyword">COMMIT</span> <span class="token punctuation">(</span>提交<span class="token punctuation">)</span><span class="token operator">|</span> <span class="token keyword">ROLLBACK</span><span class="token punctuation">(</span>回滚<span class="token punctuation">)</span><span class="token punctuation">]</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>需求1:我们需要把临场嘉宾里面的人全部调去北京德云色，然后删除临场嘉宾这个ass。</p>
<blockquote>
<p>这个需要一个更新和一个删除的两条sql语句。</p>
<p>需要事务机制保证两条都执行成功。</p>
</blockquote>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>30</td>
<td>马三立</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
<tr>
<td>31</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
<tr>
<td>32</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
<tr>
<td>33</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>50</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
<tr>
<td>10</td>
<td>临时嘉宾</td>
<td>50</td>
</tr>
</tbody></table>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209110653.png" srcset="/img/loading.gif" alt="image-20201209110653101"></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token keyword">UPDATE</span> employee
<span class="token keyword">SET</span> ass_no <span class="token operator">=</span> <span class="token number">10</span>
<span class="token keyword">WHERE</span> ass_no <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">;</span>

<span class="token keyword">DELETE</span> associations
<span class="token keyword">FROM</span> associations
<span class="token keyword">WHERE</span> ass_no <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">;</span>
<span class="token keyword">COMMIT</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>结果</p>
<table>
<thead>
<tr>
<th>id</th>
<th>ass_name</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>北京德云色</td>
<td>10</td>
</tr>
<tr>
<td>2</td>
<td>辽宁德云色</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>天津德云色</td>
<td>30</td>
</tr>
<tr>
<td>4</td>
<td>闹大利亚德云色</td>
<td>40</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th>id</th>
<th>name</th>
<th>salary</th>
<th>work</th>
<th>join_date</th>
<th>level</th>
<th>ass_no</th>
</tr>
</thead>
<tbody><tr>
<td>1</td>
<td>张三</td>
<td>12000</td>
<td>逗哏</td>
<td>1994-05-06</td>
<td>1</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>王二</td>
<td>14000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>2</td>
<td>20</td>
</tr>
<tr>
<td>5</td>
<td>于谦</td>
<td>13700</td>
<td>捧哏</td>
<td>2001-12-19</td>
<td>1</td>
<td>30</td>
</tr>
<tr>
<td>6</td>
<td>岳云鹏</td>
<td>11000</td>
<td>逗哏</td>
<td>2004-05-29</td>
<td>2</td>
<td>40</td>
</tr>
<tr>
<td>29</td>
<td>徐德亮</td>
<td>3000</td>
<td>逗哏</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>30</td>
<td>马三立</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>31</td>
<td>刘宝瑞</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>32</td>
<td>张文顺</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>33</td>
<td>侯耀文</td>
<td>1000</td>
<td>老艺术家</td>
<td>1997-05-20</td>
<td>1</td>
<td>10</td>
</tr>
</tbody></table>
<h4 id="9-1-2-事务的特性"><a href="#9-1-2-事务的特性" class="headerlink" title="9.1.2 事务的特性"></a>9.1.2 事务的特性</h4><ul>
<li><p>事务的原子性</p>
<blockquote>
<p>一个事务中的所有操作，要么全部完成，要么全部失败，事务执行后，不可能停留在中间过程的某个状态。</p>
</blockquote>
</li>
<li><p>事务的一致性</p>
<blockquote>
<p>不管在任何给定的时间，并发的事务有多少，事务必须保证运行结果的一致性。<strong>比如A向B转账，不可能A扣了钱，B却没收到。</strong></p>
</blockquote>
</li>
<li><p>事务的隔离性</p>
<blockquote>
<p>隔离性，要求事务不受其他并发事务的影响，如同在给定的时间内，该事务是该数据库中唯一运行的事物。</p>
</blockquote>
</li>
<li><p>事务的持久性</p>
<blockquote>
<p>事务一旦提交，结果便是永久性的，即使发生宕机，仍然可以靠日志完成事务的持久化。</p>
</blockquote>
</li>
</ul>
<h3 id="9-2-事务的隔离级别"><a href="#9-2-事务的隔离级别" class="headerlink" title="9.2 事务的隔离级别"></a>9.2 事务的隔离级别</h3><h4 id="9-2-0-为什么需要隔离性？"><a href="#9-2-0-为什么需要隔离性？" class="headerlink" title="9.2.0 为什么需要隔离性？"></a>9.2.0 为什么需要隔离性？</h4><blockquote>
<p>隔离性是指当多个用户并发操作数据库时，数据库为每一个用户开启不同的事务，这些事务之间相互不干扰，相互隔离。如果事务之间不是互相隔离的，可能将会出现以下问题。</p>
</blockquote>
<ul>
<li><p>脏读</p>
<blockquote>
<p>脏读（dirty read），简单来说，就是一个事务在处理过程中读取了另外一个事务未提交的数据。</p>
<p>这种未提交的数据我们称之为脏数据。依据脏数据所做的操作肯能是不正确的。</p>
</blockquote>
</li>
<li><p>幻读</p>
<blockquote>
<p>幻读（phantom read），是事务非独立执行时发生的一种现象。</p>
<p>例如事务 T1 对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务 T2 又对这个表中插入了一行数据项为“1”的数据，并且提交给数据库。</p>
<p>而操作事务 T1 的用户如果再查看刚刚修改的数据，会发现数据怎么还是 1？其实这行是从事务 T2 中添加的，就好像产生幻觉一样，这就是发生了幻读。</p>
</blockquote>
</li>
<li><p>不可重复读</p>
<blockquote>
<p>不可重复读（non-repeatable read），是指一个事务范围内，多次查询某个数据，却得到不同的结果。</p>
<p>在第一个事务中的两次读取数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能就是不一样的。</p>
</blockquote>
</li>
</ul>
<h4 id="9-2-1-事务四个隔离级别概念"><a href="#9-2-1-事务四个隔离级别概念" class="headerlink" title="9.2.1 事务四个隔离级别概念"></a>9.2.1 事务四个隔离级别概念</h4><blockquote>
<p>数据库中的事务都是并发执行的，难免有些业务要在不同事务执行的时候，相互读取临时数据，这就需要修改事务的隔离级别才能做到。</p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209154645.png" srcset="/img/loading.gif" alt="image-20201209154645728"></p>
<blockquote>
<p>从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，<strong>可重复读</strong>是 MySQL 的默认级别。</p>
</blockquote>
<p>修改隔离级别的语句是：<code>set [作用域] transaction isolation level [事务隔离级别]，</code></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">SET</span> <span class="token punctuation">[</span><span class="token keyword">SESSION</span><span class="token punctuation">(</span>只针对当前回话窗口<span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">GLOBAL</span><span class="token punctuation">(</span>全局的<span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token keyword">TRANSACTION</span> <span class="token keyword">ISOLATION</span> <span class="token keyword">LEVEL</span> &#123;<span class="token keyword">READ</span> <span class="token keyword">UNCOMMITTED</span> <span class="token operator">|</span> <span class="token keyword">READ</span> <span class="token keyword">COMMITTED</span> <span class="token operator">|</span> <span class="token keyword">REPEATABLE</span> <span class="token keyword">READ</span> <span class="token operator">|</span> <span class="token keyword">SERIALIZABLE</span>&#125;。<span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>比如下面这个语句的意思是设置全局隔离级别为读提交级别。</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql">mysql<span class="token operator">></span> <span class="token keyword">set</span> <span class="token keyword">global</span> <span class="token keyword">transaction</span> <span class="token keyword">isolation</span> <span class="token keyword">level</span> <span class="token keyword">read</span> <span class="token keyword">committed</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<p>需要注意的是，<code>begin/START TRANSACTION</code> 命令并不代表事务的开始，事务开始于<code> begin</code> 命令之后的第一条语句执行的时候。例如下面示例中，<code>UPDATE......</code> 才是事务的开始，</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span> <span class="token comment">#比如我们只执行开启事务</span>

<span class="token keyword">select</span> <span class="token operator">*</span>
<span class="token keyword">from</span> information_schema<span class="token punctuation">.</span>innodb_trx<span class="token punctuation">;</span> <span class="token comment"># 查看有多少事务在执行</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209170727.png" srcset="/img/loading.gif" alt="image-20201209170727302"></p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span>
<span class="token keyword">UPDATE</span> employee E
<span class="token keyword">SET</span> E<span class="token punctuation">.</span>ass_no <span class="token operator">=</span> <span class="token number">1</span>
<span class="token keyword">WHERE</span> ass_no <span class="token operator">=</span> <span class="token number">10</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> <span class="token operator">*</span>
<span class="token keyword">from</span> information_schema<span class="token punctuation">.</span>innodb_trx<span class="token punctuation">;</span> <span class="token comment"># 查看有多少事务在执行</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201209170814.png" srcset="/img/loading.gif" alt="image-20201209170813928"></p>
<h4 id="9-2-2-隔离级别分析"><a href="#9-2-2-隔离级别分析" class="headerlink" title="9.2.2 隔离级别分析"></a>9.2.2 隔离级别分析</h4><h5 id="9-2-2-1-READ-UNCOMMITTED-读未提交"><a href="#9-2-2-1-READ-UNCOMMITTED-读未提交" class="headerlink" title="9.2.2.1 READ UNCOMMITTED-读未提交"></a>9.2.2.1 <code>READ UNCOMMITTED</code>-读未提交</h5><blockquote>
<p>MySQL 事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销。但有利就有弊，这基本上就相当于裸奔啊，所以它连脏读的问题都没办法解决。</p>
<p>任何事务对数据的修改都会第一时间暴露给其他事务，即使事务还没有提交。</p>
</blockquote>
<p>​    实例：</p>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    c <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">Engine</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-sql" data-language="sql"><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'刘备'</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span aria-hidden="true" class="line-numbers-rows"><span></span></span></code></pre></div>
<div class="code-wrapper"><pre class="line-numbers language-shell" data-language="shell"><code class="language-shell">mysql&gt; SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 刘备   |
+----+--------+
1 row in set (0.01 sec)
<span aria-hidden="true" class="line-numbers-rows"><span></span><span></span><span></span><span></span><span></span><span></span><span></span></span></code></pre></div>
<p>是可以读取到插入语句的事务中没有提交的数据的。</p>
<p>在事务B进行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的。</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201210153045.png" srcset="/img/loading.gif"></p>
<h5 id="9-2-2-2-READ-COMMITTED-读取已提交"><a href="#9-2-2-2-READ-COMMITTED-读取已提交" class="headerlink" title="9.2.2.2 READ COMMITTED-读取已提交"></a>9.2.2.2 <code>READ COMMITTED</code>-读取已提交</h5><blockquote>
<p>既然读未提交没办法解决脏数据问题，那么就有了读提交。读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。那脏数据问题迎刃而解了。</p>
</blockquote>
<p>业务场景：银行转账消费，比如说<code>一个人账号剩5000元,A事务执行充值3000</code>，`B事务支付1000，如果B事务读取的隔离级别是读取未提交的，如果此时先去查账户余额就是8000元，支付完余额还剩7000元，但是A事务因为一些执行错误回滚了，没有充值成功，又消费了1000元，应该剩余4000元，那么账户就凭白无辜多了3000元。</p>
<p>这个时候，就不能使用读取未提交的事务隔离级别了，就需要保证读到的数据一定是提交过的，修改了数据文件的。</p>
<p>这个时候根据这个数据进行操作就会有问题。</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201210153421.png" srcset="/img/loading.gif"></p>
<p><code>从图中可以看到，第4步时，由于</code>Session B<code>中的事务尚未提交，所以</code>Session A<code>中的事务查询得到的结果只是</code>‘刘备’<code>，而第6步时，由于</code>Session B<code>中的事务已经提交，所以</code>Session B<code>中的事务查询得到的结果就是</code>‘关羽’<code>了。</code></p>
<p>对于某个处在在<code>已提交读</code>隔离级别下的事务来说，只要其他事务修改了某个数据的值，并且之后提交了，那么该事务就会读到该数据的最新值，比方说：</p>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201210153641.png" srcset="/img/loading.gif"></p>
<p>我们在<code>Session B</code>中提交了几个隐式事务，这些事务都修改了<code>id</code>为<code>1</code>的记录的列c的值，每次事务提交之后，<code>Session A</code>中的事务都可以查看到最新的值。这种现象也被称之为<code>不可重复读</code>.</p>
<h5 id="9-2-2-3-REPEATABLE-READ-重复读取"><a href="#9-2-2-3-REPEATABLE-READ-重复读取" class="headerlink" title="9.2.2.3 REPEATABLE READ-重复读取"></a>9.2.2.3 <code>REPEATABLE READ</code>-重复读取</h5><blockquote>
<p>一个事务只能读到另一个已经提交的事务修改过的数据，但是<code>第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据</code>。那么这种<code>隔离级别</code>就称之为<code>可重复读</code></p>
</blockquote>
<p><img src="http://qiniuyun.quancundexiwang.wang/20201210155546.png" srcset="/img/loading.gif"></p>
<p>从图中可以看出来，<code>Session A</code>中的事务在第一次读取<code>id</code>为<code>1</code>的记录时，列<code>c</code>的值为<code>&#39;刘备&#39;</code>，之后虽然<code>Session B</code>中隐式提交了多个事务，每个事务都修改了这条记录，但是<code>Session A</code>中的事务读到的列<code>c</code>的值仍为<code>&#39;刘备&#39;</code>，与第一次读取的值是相同的。</p>
<h5 id="9-2-2-4-SERIALIZABLE串行化"><a href="#9-2-2-4-SERIALIZABLE串行化" class="headerlink" title="9.2.2.4 SERIALIZABLE串行化"></a>9.2.2.4 <code>SERIALIZABLE</code>串行化</h5><p><img src="http://qiniuyun.quancundexiwang.wang/20201210160108.png" srcset="/img/loading.gif"></p>
<p>如图所示，当<code>Session B</code>中的事务更新了<code>id</code>为<code>1</code>的记录后，之后<code>Session A</code>中的事务再去访问这条记录时就被卡住了，直到<code>Session B</code>中的事务提交之后，<code>Session A</code>中的事务才可以获取到查询结果。</p>

            </div>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E5%9F%BA%E7%A1%80/">计算机基础</a>
                    
                  </div>
                
                
                  <div class="post-meta">
                    <i class="iconfont icon-tags"></i>
                    
                      <a class="hover-with-bg" href="/tags/mysql/">mysql</a>
                    
                  </div>
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a target="_blank" href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/posts/14576/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">JavaScript基础面试记录</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/posts/34932/">
                        <span class="hidden-mobile">Styled-Components管理组件化开发的样式</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
              <!-- Comments -->
              <article class="comments" id="comments">
                
                
  <div id="vcomments"></div>
  <script type="text/javascript">
    Fluid.utils.waitElementVisible('vcomments', function() {
      Fluid.utils.createScript('https://cdn.jsdelivr.net/npm/valine@1.4.14/dist/Valine.min.js', function () {
        new Valine({
          el: "#vcomments",
          app_id: "Mi65hxq7VAFUDwOLeIGAOgiV-gzGzoHsz",
          app_key: "hMuhiD4FRqhns4giqLiEH9HG",
          placeholder: "说点什么",
          path: window.location.pathname,
          avatar: "retro",
          meta: ["nick","mail","link"],
          pageSize: "10",
          lang: "zh-CN",
          highlight: false,
          recordIP: false,
          serverURLs: "",
        });
      });
    });
  </script>
  <noscript>Please enable JavaScript to view the
    <a target="_blank" href="https://valine.js.org" rel="nofollow noopener noopener">comments powered by Valine.</a>
  </noscript>


              </article>
            
          </article>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    

    
      <a id="scroll-top-button" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
    

    
  </main>

  <footer class="text-center mt-5 py-3">
  <div class="footer-content">
    

  </div>
  
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


  
  <!-- 备案信息 -->
  <div class="beian">
    <span>
      <a href="http://beian.miit.gov.cn/" target="_blank" rel="nofollow noopener">
        豫ICP备20009912号-1
      </a>
    </span>
    
  </div>


  
</footer>

<!-- SCRIPTS -->

  <script  src="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.jsdelivr.net/npm/nprogress@0.2.0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":true,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.min.js" ></script>
<script  src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>

<!-- Plugins -->


  
    
      <script  src="/js/lazyload.js" ></script>
    
  



  
    
  



  <script  src="https://cdn.jsdelivr.net/npm/tocbot@4.12.0/dist/tocbot.min.js" ></script>



  <script  src="https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@3.5.7/dist/jquery.fancybox.min.js" ></script>



  <script  src="https://cdn.jsdelivr.net/npm/anchor-js@4.3.0/anchor.min.js" ></script>



  <script defer src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>




  <script  src="https://cdn.jsdelivr.net/npm/typed.js@2.0.11/lib/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var title = document.getElementById('subtitle').title;
      
      typing(title)
      
    })(window, document);
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    (function () {
      var path = "http://qiniuyun.quancundexiwang.wang/xmlSearch.xml";
      var inputArea = document.querySelector("#local-search-input");
      inputArea.onclick = function () {
        searchFunc(path, 'local-search-input', 'local-search-result');
        this.onclick = null
      }
    })()
  </script>















<!-- 主题的启动项 保持在最底部 -->
<script  src="/js/boot.js" ></script>



</body>
</html>
